SQL Joins - 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

SQL Joins

Author

Topic

MagicCity77
Starting Member

19 Posts

Posted - 2009-10-13 : 12:00:23

Hello everyone I have a little issue here. Here is my code

SELECT Car.CarType 
           ,Car.Model
           ,Car.Tire
           ,Car.SupervisorID
           ,Car.ResponsibleID
           ,Car.Equipment
           ,Supervisor.SupervisorName as Supervisor
           ,Supervisor.SupervisorName as Responsible 

FROM Car
     Left Join Supervisor on Car.SupervisorID = Supervisor.SupervisorID
     Left Join Supervisor on Car.ResponsibleID = Supervisor.SupervisorID

WHERE Car.Location = @CarLocation

The problem is the supervisor and responsible columns use the same data just different IDs. How do I perform a join on the SupervisorID but still fill the ResposibleID? Most of the time they are different but there will be some instances where they are the same.

Example Supervisor ID = '2' = "Maxwell Jacobs"
ResponsibleID = '5' = "Jazmine Sullivan"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-13 : 12:05:47

Give adifferent alias to each joined Supervisor table and use it.

No, you're never too old to Yak'n'Roll if you're too young to die.

MagicCity77
Starting Member

19 Posts

Posted - 2009-10-13 : 12:16:13

So are you saing to do the following:

     Left Join Supervisor on Car.SupervisorID = Supervisor.SupervisorID
Left Join Responsible on Car.SupervisorID = Supervisor.SupervisorID

I thought the table name goes after the Left join in the left Join Statement?

I am still learning how to code SQL (OJT) so I may be miss understanding where I list the alias.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-13 : 12:19:02

[code]SELECT Car.CarType
,Car.Model
,Car.Tire
,Car.SupervisorID
,Car.ResponsibleID
,Car.Equipment
,sup.SupervisorName as Supervisor
,res.SupervisorName as Responsible

FROM Car
Left Join Supervisor as sup on Car.SupervisorID = sup.SupervisorID
Left Join Supervisor as res on Car.ResponsibleID = res.SupervisorID[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

MagicCity77
Starting Member

19 Posts

Posted - 2009-10-13 : 12:24:49

Thanks that did the trick. I did not know that I could do this in the from clause. I am learning as I code and have learned a great deal from building this .NET and connecting to this database.

Thanks again

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-13 : 12:27:39

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

MagicCity77
Starting Member

19 Posts

Posted - 2009-10-13 : 12:48:05

Next issue that I have discovered is that the responsible ID is not triggering a different name from the SupervisorID giving me the same result as the supervisorname for the supervisorID.

Anymore tips?

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-13 : 13:01:34

quote:
Originally posted by MagicCity77

Next issue that I have discovered is that the responsible ID is not triggering a different name from the SupervisorID giving me the same result as the supervisorname for the supervisorID.

Anymore tips?

open a new thread

read the hint link in my sig and post what it asks for

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources