Multiple joins on 1 table. - 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 2005 Forums

Transact-SQL (2005)

Multiple joins on 1 table.

Author

Topic

Veritas5
Starting Member

6 Posts

Posted - 2009-06-03 : 15:19:42

Hello!

I've got a small technical problem to solve. I'm working in a transportation business, so I need to manipulate data related to merchandise pickups, to vehicles informations, destinations, etc. I've got this table named Pickup_Header that holds my pickups data, and first of all, I want to get some information about the trucks that must deliver the merchandise. Consequently, I'm using a master pick up number which is going to be my key between my Pickup_Header and fastVehicles table. Up to that point, no problems, my query returns 5 records, which is normal. Where things starts to get complicated, is when I need to get the city name and state of the merchandise's destination. I've got a table named Cities filled with the full names of all cities and states of Canada and the US. The key between my Pickup_Header table and Cities table would be 2 fields named DeliveryCityCode and DeliveryStateCode. When I execute my first Inner join with the city code, no problems, I've still got 5 records. But then, when I add a second join with the state code, I get about 6000 records! Here's my query. If you could tell me what's wrong with my code and how I should proceed in order to do 2 Inner joins in 1 table. Thanks a lot!

Select *
From cisadmin.Pickup_Header PH
Inner join cisadmin.fastVehicles FV On PH.DspMasterPickupNumber = FV.MasterPickupNumber
inner join cisadmin.Cities C On PH.DeliveryCityCode = C.CityCode
inner join cisadmin.Cities C2 On PH.DeliveryStateCode = C2.StateCode

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 15:27:46

Not sure by this given information, but try this:


Select *
From cisadmin.Pickup_Header PH
Inner join cisadmin.fastVehicles FV On PH.DspMasterPickupNumber = FV.MasterPickupNumber
inner join cisadmin.Cities C On PH.DeliveryCityCode = C.CityCode
inner join cisadmin.Cities C2 On PH.DeliveryStateCode = C2.StateCode and PH.DeliveryCityCode = C2.CityCode

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

Veritas5
Starting Member

6 Posts

Posted - 2009-06-05 : 09:17:26

Thanks this works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources