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 statusFROM 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]
