This week's giveaway is in the EJB and other Java EE Technologies forum. We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line! See this thread for details.
I am facing a unique problem in mysql.
I am posting this problem in this section because it says JDBC + some brushup of SQL.
Firstly i am working on a Apps written in java & interacts with MySQL.
Heres a table EMPLOYEE
ID , Name
I have a set of input parameters. These are nothing but id of a table EMPLOYEE_CACHE (1,2,3,4,5,6,7)
I want to write a query such that i get output as 3,5
You must have guessed correctly, i want to sync EMPLOYEE_CACHE with EMPLOYEE. I have code that handles INSERT & UPDATE smoothly. Just want to add something that detects delete in master table and update the cache table
My best shot is it can be done using MINUS in mysql. But again MINUS needs 2 tables to work on. And here i have 1 table and a set of parameters.
How can i form my query here
I'm not a MySQL developer, but could you use database triggers on the master table to pass changes on to the "cache" table?
Also I don't quite understand the purpose of your EMPLOYEE_CACHE table: why do you need it at all?
You can do a MINUS e.g by comparing the list of IDs in each table, provided the ID for a given employee record never changes. But this will have to keep reading all the IDs in the two tables every time you want to check them, and that seems like a lot of unnecessary work.
If you have to keep the two tables in sync like this, use triggers.
You could also look at table replication if it's really vital to have two synchronised copies of the tables, but you'll need to talk to your DBA about this.