Page 1 of 1

Advanced query help

Posted: Thu Sep 23, 2010 8:20 am
by shags_j
Hi Guys,

I have a situation that I'm not sure how to approach. I'm sure it's easy but I can't see it...

I have two tables. Table one has three fields, a primary key and two other fields that are both linked to the primary key of the second table

ie. Table 1 = id, FK1, FK2
Table 2 = id, Name

I need to query and show in a table the result from table 1, but instead of showing the fk id, I want it to show the name that it relates to. I could do this if it was just one using a left join, but reffering to the same table twice has me stumped.

Cheers,

Shags

Re: Advanced query help

Posted: Thu Sep 23, 2010 11:39 am
by McInfo
Refer to the second table by aliases and do a second join.

Code: Select all

SELECT a.id, a.fk1, b1.name, a.fk2, b2.name
FROM a
LEFT JOIN b b1 ON (a.fk1 = b1.id)
LEFT JOIN b b2 ON (a.fk2 = b2.id)
You might consider doing inner joins if you don't want nulls in your results.

Re: Advanced query help

Posted: Thu Sep 23, 2010 3:57 pm
by shags_j
Perfect thanks. :P