SQL, Joining a table to another table (twice) - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL, Joining a table to another table (twice)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-02 : 08:27:39

Zackie writes "Hello.

I'm trying to join a table to another table in order to get a value from the second table. This needs to be done twice in the same query.

The database tables are as follows:

table SUB
sub_id
sub_title
sub_desc
sub_p_one_id
sub_p_two_id

table P
p_id
p_name

Example:

SUB

sub_id | sub_title | sub_desc | sub_p_one_id | sub_p_two_id
1 | none | none | 1 | 2
2 | n0n3 | n0ne | 3 | 3

P
p_id | p_name
1 | Zack
2 | Jay Yaj
3 | No Name
4 | Don't really care

Now I need an SQL query that returns a result set for a specified (sub_id). Example

if submited id = 1 it returns
1 | none | none | 1 | 2 | Zack | Jay Yai
if submited id = 2 it returns
2 | n0n3 | n0ne | 3 | 3 | No Name | No Name

If I had only one field to connect, I would use a simple inner join "connecting" the two tables "on sub_p_one_id = p_id".

So my question is: how can a do a similar query that joins a table to a second table twice, getting a single result set, as in the examples above.

Thanks in advance."

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-02 : 09:49:29

[code]
select a.sub_id,a.sub_title,a.sub_desc,a.sub_p_one_id, a.sub_p_two_id, b.p_name, c.p_name
from sub a
left join p b on b.p_id = a.sub_p_one_id
left join p c on c.p_id = a.sub_p_two_id
where a.sub_id = 2
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources