thanks for the reply... and my bad on the diff between the sql. the actual sql had ~100 values. (i simply cut it to demonstrate what i'm trying to do!!)
i took the two tables created by the initial sql statements and modified them so that they both had the same structure. i then added/deleted/etc.. untill i got to a table that gave me all the information i needed, but i need to figure out how to reorder it... the sql/query you provided is similar to what i had created when i 1st started. however, it only gives the actual join of the two tables. i'm really trying to get a 'merge' of the information between the two tables... here's what i have so far: table 1: mysql> select * from t1; +-----+------+-------+------+-----+--------+ | ID | type | user | ID2 | uID | status | +-----+------+-------+------+-----+--------+ | 40 | 1 | admin | 157 | 40 | NULL | | 102 | 1 | admin | 405 | 102 | NULL | | 257 | 1 | admin | 1025 | 257 | NULL | | 267 | 1 | admin | 1065 | 267 | NULL | | 379 | 1 | admin | 1513 | 379 | NULL | +-----+------+-------+------+-----+--------+ 5 rows in set (0.00 sec) table 2: mysql> select * from t2; +------+------+--------+------+------+------+ | ID | type | status | user | ID2 | uID | +------+------+--------+------+------+------+ | 40 | 1 | 0 | NULL | NULL | NULL | | 40 | 2 | 0 | NULL | NULL | NULL | | 40 | 3 | 0 | NULL | NULL | NULL | | 40 | 4 | 0 | NULL | NULL | NULL | | 102 | 1 | 0 | NULL | NULL | NULL | | 102 | 2 | 0 | NULL | NULL | NULL | | 102 | 3 | 0 | NULL | NULL | NULL | | 102 | 4 | 0 | NULL | NULL | NULL | | 257 | 1 | 0 | NULL | NULL | NULL | | 257 | 2 | 0 | NULL | NULL | NULL | | 257 | 3 | 0 | NULL | NULL | NULL | | 257 | 4 | 0 | NULL | NULL | NULL | | 267 | 1 | 0 | NULL | NULL | NULL | | 267 | 2 | 0 | NULL | NULL | NULL | | 267 | 3 | 0 | NULL | NULL | NULL | | 267 | 4 | 0 | NULL | NULL | NULL | | 379 | 1 | 0 | NULL | NULL | NULL | | 379 | 2 | 0 | NULL | NULL | NULL | | 379 | 3 | 0 | NULL | NULL | NULL | | 379 | 4 | 0 | NULL | NULL | NULL | | 394 | 1 | 0 | NULL | NULL | NULL | | 394 | 2 | 0 | NULL | NULL | NULL | | 394 | 3 | 0 | NULL | NULL | NULL | | 394 | 4 | 0 | NULL | NULL | NULL | | 460 | 1 | 0 | NULL | NULL | NULL | | 460 | 2 | 0 | NULL | NULL | NULL | | 460 | 3 | 0 | NULL | NULL | NULL | | 460 | 4 | 0 | NULL | NULL | NULL | | 541 | 1 | 0 | NULL | NULL | NULL | | 541 | 2 | 0 | NULL | NULL | NULL | | 541 | 3 | 0 | NULL | NULL | NULL | | 541 | 4 | 0 | NULL | NULL | NULL | | 560 | 1 | 0 | NULL | NULL | NULL | | 560 | 2 | 0 | NULL | NULL | NULL | | 560 | 3 | 0 | NULL | NULL | NULL | | 560 | 4 | 0 | NULL | NULL | NULL | +------+------+--------+------+------+------+ 36 rows in set (0.00 sec) table td: mysql> select * from td; +------+------+--------+------+------+------+ | ID | type | status | user | ID2 | uID | +------+------+--------+------+------+------+ | 40 | 2 | 0 | NULL | NULL | NULL | | 40 | 3 | 0 | NULL | NULL | NULL | | 40 | 4 | 0 | NULL | NULL | NULL | | 102 | 2 | 0 | NULL | NULL | NULL | | 102 | 3 | 0 | NULL | NULL | NULL | | 102 | 4 | 0 | NULL | NULL | NULL | | 257 | 2 | 0 | NULL | NULL | NULL | | 257 | 3 | 0 | NULL | NULL | NULL | | 257 | 4 | 0 | NULL | NULL | NULL | | 267 | 2 | 0 | NULL | NULL | NULL | | 267 | 3 | 0 | NULL | NULL | NULL | | 267 | 4 | 0 | NULL | NULL | NULL | | 379 | 2 | 0 | NULL | NULL | NULL | | 379 | 3 | 0 | NULL | NULL | NULL | | 379 | 4 | 0 | NULL | NULL | NULL | | 394 | 1 | 0 | NULL | NULL | NULL | | 394 | 2 | 0 | NULL | NULL | NULL | | 394 | 3 | 0 | NULL | NULL | NULL | | 394 | 4 | 0 | NULL | NULL | NULL | | 460 | 1 | 0 | NULL | NULL | NULL | | 460 | 2 | 0 | NULL | NULL | NULL | | 460 | 3 | 0 | NULL | NULL | NULL | | 460 | 4 | 0 | NULL | NULL | NULL | | 541 | 1 | 0 | NULL | NULL | NULL | | 541 | 2 | 0 | NULL | NULL | NULL | | 541 | 3 | 0 | NULL | NULL | NULL | | 541 | 4 | 0 | NULL | NULL | NULL | | 560 | 1 | 0 | NULL | NULL | NULL | | 560 | 2 | 0 | NULL | NULL | NULL | | 560 | 3 | 0 | NULL | NULL | NULL | | 560 | 4 | 0 | NULL | NULL | NULL | +------+------+--------+------+------+------+ 31 rows in set (0.00 sec) mysql> select * from ta; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< how to reorder +------+------+--------+------+------+------+ | ID | type | status | user | ID2 | uID | +------+------+--------+------+------+------+ | 40 | 1 | admin | 157 | 40 | NULL | | 102 | 1 | admin | 405 | 102 | NULL | | 257 | 1 | admin | 1025 | 257 | NULL | | 267 | 1 | admin | 1065 | 267 | NULL | | 379 | 1 | admin | 1513 | 379 | NULL | | 40 | 2 | 0 | NULL | NULL | NULL | | 40 | 3 | 0 | NULL | NULL | NULL | | 40 | 4 | 0 | NULL | NULL | NULL | | 102 | 2 | 0 | NULL | NULL | NULL | | 102 | 3 | 0 | NULL | NULL | NULL | | 102 | 4 | 0 | NULL | NULL | NULL | | 257 | 2 | 0 | NULL | NULL | NULL | | 257 | 3 | 0 | NULL | NULL | NULL | | 257 | 4 | 0 | NULL | NULL | NULL | | 267 | 2 | 0 | NULL | NULL | NULL | | 267 | 3 | 0 | NULL | NULL | NULL | | 267 | 4 | 0 | NULL | NULL | NULL | | 379 | 2 | 0 | NULL | NULL | NULL | | 379 | 3 | 0 | NULL | NULL | NULL | | 379 | 4 | 0 | NULL | NULL | NULL | | 394 | 1 | 0 | NULL | NULL | NULL | | 394 | 2 | 0 | NULL | NULL | NULL | | 394 | 3 | 0 | NULL | NULL | NULL | | 394 | 4 | 0 | NULL | NULL | NULL | | 460 | 1 | 0 | NULL | NULL | NULL | | 460 | 2 | 0 | NULL | NULL | NULL | | 460 | 3 | 0 | NULL | NULL | NULL | | 460 | 4 | 0 | NULL | NULL | NULL | | 541 | 1 | 0 | NULL | NULL | NULL | | 541 | 2 | 0 | NULL | NULL | NULL | | 541 | 3 | 0 | NULL | NULL | NULL | | 541 | 4 | 0 | NULL | NULL | NULL | | 560 | 1 | 0 | NULL | NULL | NULL | | 560 | 2 | 0 | NULL | NULL | NULL | | 560 | 3 | 0 | NULL | NULL | NULL | | 560 | 4 | 0 | NULL | NULL | NULL | +------+------+--------+------+------+------+ 36 rows in set (0.00 sec) if i could figure out how to reorder the table, to group all the 'ID' together, followed by the 'type'. thanks -bruce -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 29, 2004 7:27 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: merging of two tables using temp tables??? Bruce, My first thought was the LEFT JOIN Jacques suggested. My second thought was that it would be better to just create a single query that gets what you want in the first place. So I looked a little closer, and now I'm confused. At the end of your message, you include 2 queries that you say were used to create the 2 tables, but the first table was definitely not created with the first query, as it has 2 more columns then are selected in the first query. (I see you've just posted a different 1st query in a follow-up message, though it still doesn't quite match the output.) There also seems to be a mismatch between the 2 queries. The first query (replacing your multiple OR conditions with the easier to read IN) selects rows with universityTBL.ID IN ('40', '102', '257', '267', '379', '394'); but the second query selects rows with universityID IN ('40', '102', '257', '267', '379', '394', '460', '541', '560') Why the 3 extra values in the second query? I'm guessing that your goal is simply to add the status column to the results of query 1. In that case, I'd expect that something like SELECT u2.universityID as ID, u2.urltype as type, u4.username as user, u3.itemID as ID2, u1.ID as uID, p1.start_status as status FROM universityTBL as u1 LEFT JOIN university_urlTBL as u2 ON u2.universityID = u1.ID RIGHT JOIN parsefileTBL as p1 ON p1.university_urlID = u2.ID LEFT JOIN user_rolesTBL as u3 ON u3.itemID = u2.ID LEFT JOIN users as u4 ON u3.userID = u4.user_id JOIN latestParseStatusTBL as l1 ON p1.fileID = l1.itemID WHERE u2.urltype = u3.itemType AND u2.urltype IN (1, 3) AND u3.process = 20 AND u1.ID IN (40, 102, 257, 267, 379, 394); would do the trick. Note that I took out the quotes around the integers. Your IDs are integers, not strings, right? Michael bruce wrote: > thanks for the response... > > however, the attempt at using the left join/where construct was what i had > initially tried, with no luck. (which is not to say i was implementing it > correctly!!) > > using the basic select * from/left join on /where seems to give a resulting > table that only contains a subset of the two tables. > > i'd like more of a merge. a thought i had was to do a simple merge, and then > somehow do an operation which would give me the rows that weren't in the > initial merge, followed by a 3rd operation that would then join the 1st two > results.... > > my gut tells me i'm making this too complex because i can't figure out how > to properly/correctly accomplish the original merging process/function to > combine the initial two tables.... > > thanks > > -bruce > > i have a situation where i create the following tables via two different > select sql statements. > > +-----+------+-------+------+-----+ > | ID | type | user | ID | uID | > +-----+------+-------+------+-----+ > | 40 | 1 | admin | 157 | 40 | > | 102 | 1 | admin | 405 | 102 | > | 257 | 1 | admin | 1025 | 257 | > | 267 | 1 | admin | 1065 | 267 | > | 379 | 1 | admin | 1513 | 379 | > +-----+------+-------+------+-----+ > 5 rows in set (0.00 sec) > > +------+------+--------+ > | ID | type | status | > +------+------+--------+ > | 40 | 1 | 0 | > | 40 | 2 | 0 | > | 40 | 3 | 0 | > | 40 | 4 | 0 | > | 102 | 1 | 0 | > | 102 | 2 | 0 | > | 102 | 3 | 0 | > | 102 | 4 | 0 | > | 257 | 1 | 0 | > | 257 | 2 | 0 | > | 257 | 3 | 0 | > | 257 | 4 | 0 | > | 267 | 1 | 0 | > | 267 | 2 | 0 | > | 267 | 3 | 0 | > | 267 | 4 | 0 | > | 379 | 1 | 0 | > | 379 | 2 | 0 | > | 379 | 3 | 0 | > | 379 | 4 | 0 | > | 394 | 1 | 0 | > | 394 | 2 | 0 | > | 394 | 3 | 0 | > | 394 | 4 | 0 | > | 460 | 1 | 0 | > | 460 | 2 | 0 | > | 460 | 3 | 0 | > | 460 | 4 | 0 | > | 541 | 1 | 0 | > | 541 | 2 | 0 | > | 541 | 3 | 0 | > | 541 | 4 | 0 | > > > i'd like to be able to merge/combine the two tables so that i get > +------+------+--------+------+-------+-----+ > | ID | type | status | user | ID | uID | > +------+------+--------+------+-------+-----+ > | 40 | 1 | 0 | > | 40 | 2 | 0 | . > | 40 | 3 | 0 | . > | 40 | 4 | 0 | > | 102 | 1 | 0 | > | 102 | 2 | 0 | > | 102 | 3 | 0 | > | 102 | 4 | 0 | > > with the appropriate information in the various columns/rows... > > i'm looking to be able to fill the resulting table with the information if > it's present, or to have nulls/'0' where the information isn't available... > > i'd prefer to do this in mysql if possible, as my gut tells me the operation > would be faster/more efficient in mysql, than if i coded this in php/perl... > > i believe that i's need to create a temp table based on each select, and > then some how merge the two temp tables, and finally do a select on the > resulting table to get the values i need... > > looking through google/mysql hasn't shed any light on this one... > > any ideas/thoughts/comments on how i can do this..... > > > -----Original Message----- > From: Jacques Jocelyn [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 28, 2004 10:20 PM > To: bruce; [EMAIL PROTECTED] > Subject: Re: merging of two tables using temp tables??? > > > > > > Hello bruce, > > Wednesday, September 29, 2004, 6:57:34 AM, you wrote: > > b> hi.... > > > > b> i'd like to be able to merge/combine the two tables so that i get > b> +------+------+--------+------+-------+-----+ > b> | ID | type | status | user | ID | uID | > b> +------+------+--------+------+-------+-----+ > b> | 40 | 1 | 0 | > b> | 40 | 2 | 0 | . > b> | 40 | 3 | 0 | . > b> | 40 | 4 | 0 | > ... > b> with the appropriate information in the various columns/rows... > > b> i'm looking to be able to fill the resulting table with the information > if > b> it's present, or to have nulls/'0' where the information isn't > available... > > I was about to say it's easy ;-) then I saw your ps section :-o > anyway, the idea I had may give a way to start : > insert NEW_TABLE(ID,type,status,user,ID,uID) > select ID,type,status,user,ID,uID > from table1 > left join table2 on ... > where ... > > > hope that helps. > > Best regards, > Jacques Jocelyn > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
