aspose file tools*
The moose likes JDBC and the fly likes Using MINUS of MySQL with a table & set of input parametes. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using MINUS of MySQL with a table & set of input parametes." Watch "Using MINUS of MySQL with a table & set of input parametes." New topic
Author

Using MINUS of MySQL with a table & set of input parametes.

sumit anand kumar
Ranch Hand

Joined: Apr 28, 2010
Posts: 83
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
1,qqq
2,www
4,rere
6,gerwwe
7,gegwert
8,gsfdjg;

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
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1777
    
  16

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.

Or better yet, just have one table instead!

No more Blub for me, thank you, Vicar.
 
 
subject: Using MINUS of MySQL with a table & set of input parametes.