Multiple Join - SQL Server Forums

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)

Multiple Join

Author

Topic

josethegeek
Starting Member

45 Posts

Posted - 2005-01-04 : 14:02:52

Okay, this is something that I've come across multiple times, and instead of using the newer JOINS syntax I've stuck to the older syntax. I finally want to figure out how to join 3 tables using Joins.

Here is a scenario.

I have a group that has people and those people can have friends.

Here are the tables.

tbl_Groups
Group_ID
Group_Name

tbl_People
People_ID
Group_ID
People_FirstName
People_LastName

tbl_Friends
People_ID
Friend_ID

I would like to do an INNER JOIN on Group and People, and do a LEFT JOIN on people and friends

I was able to do this with the older join syntax

SELECT tbl_People.People_FirstName, tbl_People.People_LastName FROM tbl_People, tbl_Groups, tbl_Friends WHERE tbl_Groups.Group_ID = tbl_People.Group_ID AND tbl_People.People_ID *= tbl_Friends.People_ID

Could someone show me how to convert this to the newer syntax?

Thanks,
Jose

deangc
Starting Member

3 Posts

Posted - 2005-01-04 : 14:23:47

The syntax is actually clearer once you get used to it.


SELECT tbl_People.People_FirstName, 
       tbl_People.People_LastName 
FROM   tbl_People 
JOIN   tbl_Groups 
ON     tbl_Groups.Group_ID  = tbl_People.Group_ID
LEFT OUTER JOIN tbl_Friends
ON     tbl_People.People_ID = tbl_Friends.People_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources