Comparing 2 tables

[Jon Summers]

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..

[Stan James]

Here's an age-old algorithm for file matching. We can use it with two result sets (A and B) too.

Get First A
Get First B
while more A exist or more B exist
   if KeyA < KeyB
      do A Only routine
      Get Next A
   else
   if KeyB < KeyA 
      do B Only routine
      Get Next B
   else
   if KeyA = KeyB
      do Match routine
      Get Next A
      Get Next B
end while

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]

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.

[Joel McNary]

Try:

INSERT INTO TABLE_3 (SELECT * FROM TABLE_1 UNION (SELECT * FROM TABLE_2) ORDER BY A_DATE)

[Jon Summers]

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]

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]

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]

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]

No. Try this (pseudo)code:

//get the result lists
//Be sure to ORDER BY, or else this will not work...
resultsA = database.query("SELECT * FROM TABLE1 ORDER BY MY_DATE;");
resultsB = database.query("SELECT * FROM TABLE2 ORDER BY MY_DATE;");
//Get the first result from each result list
itemA = resultsA.getNextResult();
itemB = resultsB.getNextResult();
while(itemA != null && itemB != null){
    //Compare the dates
    dateA = itemA.getMyDate();
    dateB = itemB.getMyDate();
    //if dateB is earlier
    if(dateA.compareTo(dateB) > 0){
        database.insertRecord(itemB);
        itemB = resultsB.getNextResult();
    }
    //If dateA is earlier (or if they are equal)
    else{
        database.insertRecord(itemA)
        itemA = resultsA.getNextResult()
    }
}
while(itemA != null){
    database.insertRecord(itemA);
    itemA = resultsA.getNextResult();
}
while(itemB != null){
    database.insertRecord(itemB);
    itemB = resultsB.getNextResult();
}

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]

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:

if ( KeyA < KeyB ) or ( B is at end )
   do A Only routine

I'd rather do one of those than add the other loops on the end.

[Jon Summers]

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]

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:

lastDateA = null;
while(itemA != null){
    if(itemA.getMyDate() == null){
        itemA.setMyDate(lastDateA);
    }
    else{
        lastDateA = itemA.getMyDate();
    }
    dateA = lastDateA;
    unionList.add(itemA);
}

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

Object array[] = unionList.toArray();
java.util.Arrays.sort(array, new java.util.Comparator(){
    public int compare(Object obj1, Object obj2){
         return obj1.getMyDate().compareTo(obj2.getMyDate());
    }
});

(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:

for(int index = 0; index < array.length; index++){
    database.insert(array[index]);
}

[Jon Summers]

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]

Looks promising. BTW: I wrote all three if tests in my first example; the third is really a "fall through" condition

if a < b
   do one
else
if b < a
   do two
else
// we know they are equal
   do three

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]

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.