...

View Full Version : mySQL query with 3 tables



ccurle
01-17-2007, 03:58 AM
I have to query 3 tables from a database. I am just using abbreviated table description since the tables are very large

Table A
ID_A
Name_A

Table B
ID_B
Name_B

Table C
ID_C
ShowName
ID_A
ID_B

Now, I need to query all 3 and have Table A and B show the name

Table C is the main table that I need. Table A and B are reference tables.

If I do a select TableA.Name_A, TableB.Name_B, TableC.ShowName WHERE TableC.ID_A = TableA.ID_A and TableC.ID_C = TableB.ID_B

It only shows the records in Table C that have a reference to Table A and B. If there is nothing in those fields, then it doesn't show. How can I query C to show all the records and the ID_A and ID_B to show their names if there is data, if not, just leave blank?

guelphdad
01-17-2007, 12:58 PM
Don't use list syntax when you are joining tables, use explicit on clauses.

Change this style:


select TableA.Name_A, TableB.Name_B, TableC.ShowName WHERE TableC.ID_A = TableA.ID_A and TableC.ID_C = TableB.ID_B


to this style and stick with it:



SELECT
TableA.Name_A,
TableB.Name_B,
TableC.ShowName
FROM
TableC
INNER JOIN
TableA
ON
TableC.ID_A = TableA.ID_A
INNER JOIN
TableB
ON TableC.ID_C = TableB.ID_B


list syntax does not allow you to mix in outer joins without running into errors. you need LEFT JOINS if you still want info from your tables even if it doesn't match that in the other tables, so change INNER JOIN above to LEFT OUTER JOIN to get what you need.

ccurle
01-17-2007, 02:57 PM
left outer join did the trick. THANK YOU. THANK YOU. THANK YOU.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum