In a message dated 1/12/02 10:48:45 AM Central Standard Time, [EMAIL PROTECTED] writes: ---snip--- << SELECT A.Name, B.Name, P.ID FROM Persons P LEFT JOIN QualityA A USING(A_ID) LEFT JOIN QualityB B USING(B_ID) WHERE P.ID = thatGuy; >> ---snip---
That doesn't work, get an error as it tries to find B_ID in the A table. I tried swapping the partners on the first LEFT JOIN... SELECT A.Name, B.Name, P.ID FROM QualityA A LEFT JOIN Persons P USING(A_ID) LEFT JOIN QualityB B USING(B_ID) WHERE P.ID = thatGuy but that gives me more strange results (using my example IDs:) Name Name Researcher "A Quality" "AQuality" thatGuy "B Quality" "B Quality" thatGuy Here is the actual query and results, using the IDs that I am really using: SELECT L.Name, C.Name, R.Researcher FROM Languages L LEFT JOIN Researchers2Languages R USING(Language) LEFT JOIN Competencies C USING(Competence) WHERE R.Researcher = 39 LIMIT 0, 30 Name Name Researcher English English 39 Can read Can read 39 The correct results would be L.Name C.Name R.Researcher English Native speaker 39 German Can read 39 Trying to think of anything that might be use, I'll mention that I'm using SMALLINT for the foreign key "Language" and TINYINT for the "Competency" FK. In this situation, I *could* hard code C data into the A table, but I use similar constructs all the time and need to get a handle on this. If I need to describe the conditions better, I will be happy to. Thanks in advance for any suggestions. James Birkholz Admin of Posen-L mailing list & website --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php