• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Devaka Cooray
  • Ron McLeod
  • Jeanne Boyarsky
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Martijn Verburg
  • Frits Walraven
  • Himai Minh

Comparing 2 tables

 
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,
I have 2 tables that have similar fields. One of the field is Date.
I want to compare dates from both table and which is greater put that value first in the NEW table and so on...so the new table will have all the values from both tables in date order. Im using mysql. Can someone help me out with the code or any suggestions..Thanks for the help..
 
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here's an age-old algorithm for file matching. We can use it with two result sets (A and B) too.

Hope that's enough of a hint. I left out how to handle hitting the end of one set or the other. Do you see where to add that checking?
 
Stan James
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
D'oh! Forgot that if the two source queries have identical columns, you can UNION the two queries and do this all in SQL! But then it's not even a Java question.
 
Bartender
Posts: 1844
Eclipse IDE Ruby Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Try:
 
Jon Summers
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hey Stan,
I don't think mysql supports UNION..i could've done it using a VIEW but mysql doesn't even support that...Thats why i asked here if there was any other way..THanks..
 
Jon Summers
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello, in your file matching example. You said 'Get First A'..i know how to get all the fields by doing while (rs.next())..but how do i get the FIRST A ad first B.
then the next command is "while moreA exist or more B exist).thatis jsut while(rs.next() || rs1.next())
if key A < keyB // im not sure wut to put here, shouldi compare the dates that i got from rs.next and rs.next1???
Get Next A (that should be just FIRST A++...right??
And how would i account for situation if one table ends before the other??
I'll really appreciate any help you could provide me..Thanks..
 
Joel McNary
Bartender
Posts: 1844
Eclipse IDE Ruby Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, MySQL does NOT support UNION. How annoying.
Yes, the key you would compare would be the Date.
Once one of the results lists run out, you simply insert the remainder of the items from the other list until it runs out.
Alternatively, you could try this little trick to mimic UNION in a MySQL DB.
Or, you could upgrade to MySQL 4.0, which does support UNION
Should you go the algorithmic method, just remember to add an ORDER BY clause in your two SQL queries; it won't work otherwise. (This little algorithm is the key part to what is known as a Merge Sort, BTW).
[ July 15, 2003: Message edited by: Joel McNary ]
 
Jon Summers
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think im going to have to go with file example..because i can't use ORDER BY in select statement when inserting the values into other table either..
i still don't know wut you meant by "GET FIRST A"..does that mean get all the values from A first doing while(rs.next())??..if YES....so from one first table i would get
String date = rs.getTimestamp("date"); and then from the second Seelct statement, do this:
while (rs1.next())
String date1 = rs.getTimestamp("date");
And then do:
while (rs.next() || rs1.next())
if ( date.equals("date1"))
// insert the value in the new table
date ++;
else and so on???
Is that right..Could you please confirm this..Thanks a lot..
 
Joel McNary
Bartender
Posts: 1844
Eclipse IDE Ruby Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No. Try this (pseudo)code:

None of these methods are real, of course (except for the compareTo method), but they should give you a general idea of how this works.
Generally speaking, any getNext... method will return the first record the first time it is called. That is, it treats the list as a stack and just pops teh next item off the stack. In this example, it returns null if there are no items left on the stack, but generally they throw exceptions.
Hope that this makes things clearer.
 
Stan James
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Joel's example nicely turned some of my generic stuff into real JDBC terms.
You can indicate the end of one set or the other by creating an impossibly high key. Then the legit keys in the other set will always be lower and the other set will process to end. But it's more expressive to modify the test:

I'd rather do one of those than add the other loops on the end.
 
Jon Summers
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Joel and Stan,
The one problem i forgot to mention is (sorrY) is that the date fields that im supposed to orderby could be null. If they are null, im suppose to ASSUME their value is equal to the previous non-null date value (assume not set it to that value)..how would i handle that???..to counter this, in my 2 select statements im getting thevalues in order of session id (which is auto-increment)..but how do i assume it to the previous non-null when comparing??>.Can i email you my code or how do i post it here in the code tages that you guyz are using???THanks alot for your help guyz..i really really appreciate it..
 
Joel McNary
Bartender
Posts: 1844
Eclipse IDE Ruby Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Umm...that could be a problem, particularily since a generic database is not garunteed to return results in any specific order, unless there is an explicit ORDER BY clause. Some Databases return results in order of insertion, others return results in order of modification (with the lastest modificatiosn being last). This is not something that you could/should depend on. I hope that there is some other field that you can use to perform you ORDER BY, otherwise this might not work.
At this point, since I can't garuntee that the results will come back in date order, I would read the results in whatever order they come back in and place the results in a List (I'll call the variable unionList). While reading the results, I would adjust the dates as needed:

Repeat for looping through resultsB.
Then, translate the List to an array, write a comperator, and Sort.

(Note the use of the anonymous inner class--you can write a standard class if you're more comforatble doing things that way; also, that class won't work as coded--you'll have to do casting and type checking and that sort).
Finally, iterate through the sorted array and place the items back in the database:
 
Jon Summers
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hey Joel,
if i were to order it by some other field..would this code be write then??
con = Util.getDbConnection();
java.sql.Statement stmt = con.createStatement();
java.sql.Statement stmt2 = con.createStatement();
query1 = "Select * from session ORDER BY sessionid";
java.sql.ResultSet rs = stmt.executeQuery(query1);
query2 = "Select * from session1 ORDER BY sessionid";
java.sql.ResultSet rs1 = stmt2.executeQuery(query2);

rs.next();
rs1.next();

while (rs.next() || rs1.next())
{
int sessionid = rs.getInt("sessionid");
String performerid = rs.getString("performerid");
Stringpasscode = rs.getString("passcode");
int nsessionid = rs1.getInt("sessionid");
String nperformerid = rs1.getString("performerid");
String npasscode = rs1.getString("passcode");
if(sessionid>nsessionid)
//insert the rows
else if(sessionid <nsessionid)
// insert the rows
else if (sessionid == nsessionid)
//insert the rows
and So, on.......
 
Stan James
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Looks promising. BTW: I wrote all three if tests in my first example; the third is really a "fall through" condition

It would please me to see all three tests, but it would also please me for every if-else-if structure to have a "default" clause. Guess I'm just easy to please!
 
Joel McNary
Bartender
Posts: 1844
Eclipse IDE Ruby Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Looks like a good start to me, too. I just want to add that you can get "nice" formatting of your code by placing it betwee [ CODE ] and [ /CODE ] tags, or by pressing the "CODE" button in the message-edit page and placing your code between the tags. (i.e., you can write them by hand or have the computer do it for you....)
As Stan said, he included all three options because you might want to do something different if they are equal; however, it seems to me that a simple if-else is needed. That is, if B comes before A, place B first, otherwise (A comes before B or A == B), place A first.
Come to think of it, if you are using the java.util.Arrays.sort() method, you don't need to do the comparisons. Just put all the results into the list (making sure that they have their dates populated) and run the sort. No comparisons need to be done by you.
 
His brain is the size of a cherry pit! About the size of this ad:
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic