Advanced query help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
shags_j
Forum Newbie
Posts: 9
Joined: Sun Aug 22, 2010 9:55 pm

Advanced query help

Post 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
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Advanced query help

Post 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.
shags_j
Forum Newbie
Posts: 9
Joined: Sun Aug 22, 2010 9:55 pm

Re: Advanced query help

Post by shags_j »

Perfect thanks. :P
Post Reply