Multi joins on a table.

[Veritas5]

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

[Nedals]

SELECT * FROM cisadmin.Pickup_Header PH
LEFT JOIN cisadmin.fastVehicles FV ON PH.DspMasterPickupNumber = FV.MasterPickupNumber
LEFT JOIN cisadmin.Cities C ON PH.DeliveryCityCode = C.CityCode
LEFT JOIN cisadmin.Cities C2 ON PH.DeliveryStateCode = C2.StateCode

Use a LEFT JOIN instead.
And I would strongly recomend that you do not use SELECT * FROM, but instead define the columns you want returned. That way, if ever you alter the table (which you will), you don't need to go back and fix ALL the SQL statements

[chazzy]

so for a given record in cisadmin.pickup_header it has columns DeliveryCityCode and DeliveryStateCode. Also, for a given record in cisadmin.cities it has columns CityCode and StateCode, right?

so then your inner join would just be...



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 AND PH.DeliveryStateCode = C.StateCode

[Veritas5]

Thanks a lot! This works out great :)