If you post your two "original" SQL statements (the ones you use to build your example tables) I think I can help you to merge your results, possibly without the need for temporary tables. Also, what version of MySQL are you using?
Shawn Green Database Administrator Unimin Corporation - Spruce Pine "bruce" <[EMAIL PROTECTED]> wrote on 09/29/2004 12:57:34 AM: > hi.... > > 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..... > > thanks... > > -bruce > > > ps... > > the actual select sql used to create the 2 tbls are listed: > select > u4.username as user, > u3.itemID as ID, > u1.ID as uID > 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 > where u2.urltype = u3.itemType > and u2.urltype = '1' > and u3.process = '20' > and (u1.ID='40' or u1.ID='102' or u1.ID='257' or u1.ID='267' > or u1.ID='379' or u1.ID='394'); > > select u1.universityID as ID, u1.urltype as type, p1.start_status as status > from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID = > p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID > where u1.universityID='40' or u1.universityID='102' or u1.universityID='257' > or u1.universityID='267' or u1.universityID='379' or u1.universityID='394' > or u1.universityID='460' or u1.universityID='541' or u1.universityID='560' > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
