www.webdeveloper.com
Results 1 to 4 of 4

Thread: Multi joins on a table.

  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Multi joins on a table.

    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
    Attached Images Attached Images

  2. #2
    Join Date
    Dec 2002
    Location
    Pleasanton, CA
    Posts
    2,132
    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

  3. #3
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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...

    Code:
    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
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  4. #4
    Join Date
    Jun 2009
    Posts
    3
    Thanks a lot! This works out great

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles