Trouble with a JOIN statement - 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

General SQL Server Forums

New to SQL Server Programming

Trouble with a JOIN statement

Author

Topic

potn1
Starting Member

33 Posts

Posted - 2010-03-04 : 16:10:49

So I have a pretty good understanding on what I'm trying to SELECT and JOIN from my tables. There are stories on our website and they sometimes contain 2 schools. I am looking to display these school names in labels. The Article table contains a SchoolID column and the School table contains the 'Name' of the actual schools. Both of these tables JOIN by the SchoolID. I am able to grab the first School 'Name' as the SchoolName but when I try to grab the second school 'Name' as SchoolName2 I am just grabbing the first name again. I figure this is happening because I am not able to distinguish the 'Name' based off of the JOIN statement. So essentially School.Name is just grabbing the first school twice. Anyone have any ideas on this? Thanks in advance.

SELECT *,School.Name AS SchoolName, School.Name AS SchoolName2
FROM Article 
LEFT JOIN School ON School.SchoolID = Article.SchoolID OR School.SchoolID = Article.SchoolID2

gavakie
Posting Yak Master

221 Posts

Posted - 2010-03-04 : 17:31:05

Alias your table your pulling from like a and b then pull a.school name b.schoolname

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-04 : 18:07:56

SELECT S1.Name AS SchoolName, S2.Name AS SchoolName2
FROM Article
LEFT JOIN School S1 ON School.SchoolID = Article.SchoolID
LEFT JOIN School S2 ON School.SchoolID = Article.SchoolID2

subhash chandra
Starting Member

40 Posts

Posted - 2010-03-05 : 08:47:57

The correct query is:

SELECT S1.Name AS SchoolName, S2.Name AS SchoolName2
FROM Article
LEFT OUTER JOIN School S1 ON S1.SchoolID = Article.SchoolID
LEFT OUTER JOIN School S2 ON S2.SchoolID = Article.SchoolID2

Regards,
Subhash Chandra
http://SQLReality.com/blog/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 09:38:02

quote:
Originally posted by subhash chandra

The correct query is:

SELECT S1.Name AS SchoolName, S2.Name AS SchoolName2
FROM Article
LEFT OUTER JOIN School S1 ON S1.SchoolID = Article.SchoolID
LEFT OUTER JOIN School S2 ON S2.SchoolID = Article.SchoolID2

Regards,
Subhash Chandra
http://SQLReality.com/blog/

LEFT and LEFT OUTER are same. So this is same as what RobertKaucher posted before

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

potn1
Starting Member

33 Posts

Posted - 2010-03-05 : 10:43:24

Thank you everyone, this is exactly what I was looking for. I see what I was missing when I needed to create an alias for School to grab the multiple SchoolID. This is what I eneded up using and it worked. Thanks again.

SELECT S1.Name AS SchoolName, S2.Name AS SchoolName2
FROM Article
LEFT JOIN School S1 ON S1.SchoolID = Article.SchoolID
LEFT JOIN School S2 ON S2.SchoolID = Article.SchoolID2

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-06 : 22:31:27

You are quite welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources