SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question about a join query

    I have two tables that I'm combining together in a join. I have a carrier name and service name that is provided externally and I want the query to return the carrier_id and (if it exists), the service_id. If the service_id doesn't exist, then I want the returned service_id to be null. If the carrier name doesn't exist, I want the query to return empty. Is this a LEFT OUTER JOIN?

    Here is the table structure:

    Table 1: carriers
    Fields:
    carrier_id (int)
    carrier_name (varchar 32)

    Table 2: services
    Fields:
    carrier_id (int)
    service_id (int)
    service_name (varchar 32)

    And here is my failed attempt to solve this myself:

    Code:
    SELECT 
       c.carrier_id
       , cs.service_id 
    FROM 
      api_carriers as c 
      LEFT OUTER JOIN 
      api_carrier_services as cs 
    WHERE 
      c.carrier_name='FedEx' AND 
      c.carrier_id=cs.carrier_id AND 
      cs.service_name='Next Day Air'
    Thank you for your help!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Yes, it's a left outer join. But you're missing the ON clause, and don't put WHERE conditions on the second table, or the LEFT JOIN will become an INNER JOIN. Instead, put them in the ON clause as well:
    Code:
    SELECT 
        c.carrier_id
      , cs.service_id 
    FROM api_carriers as c 
    LEFT OUTER JOIN api_carrier_services as cs 
    ON  c.carrier_id=cs.carrier_id
    AND cs.service_name='Next Day Air'
    WHERE c.carrier_name='FedEx' AND

  3. #3
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by whitemank View Post
    I have two tables that I'm combining together in a join. I have a carrier name and service name that is provided externally and I want the query to return the carrier_id and (if it exists), the service_id. If the service_id doesn't exist, then I want the returned service_id to be null. If the carrier name doesn't exist, I want the query to return empty. Is this a LEFT OUTER JOIN?
    Correct!

    Code:
    SELECT 
       c.carrier_id
       , cs.service_id 
    FROM 
      api_carriers as c 
      LEFT OUTER JOIN 
      api_carrier_services as cs 
    WHERE 
      c.carrier_name='FedEx' AND 
      c.carrier_id=cs.carrier_id AND 
      cs.service_name='Next Day Air'
    There are two problems with this query
    1) WHERE c.carrier_id=cs.carrier_id
    The problem with putting this in the WHERE clause is that it has to hold for row from c and cs to be included in the results. However, you stated that it might be possible that for a particular carrier no carrier_service has to exist. This part of the WHERE clause is basically undoing the LEFT OUTER JOIN and making it an INNER JOIN.
    So in that case you should make this the ON clause of the JOIN, not put it in the WHERE clause.
    At this point your query would be:

    Code MySQL:
    SELECT 
       c.carrier_id
       , cs.service_id 
    FROM 
      api_carriers as c 
      LEFT OUTER JOIN 
      api_carrier_services as cs
      ON
      c.carrier_id=cs.carrier_id
    WHERE 
      c.carrier_name='FedEx' AND
      cs.service_name='Next Day Air'

    2) WHERE cs.service_name='Next Day Air'
    You are contradicting yourself here. You want to have the carrier_service to be NULL if it doesn't exist, but at the same time you're putting in the query that you only want the services for which the service_name is 'Next Day Air'.
    Since 'Next Day Air' <> NULL you'll want to make up your mind a bit as to what you actually want here

    Edit:


    Guido beat me to it ...
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you guys rock

    i should also like to point out that returning only those two id columns in the SELECT clause is a bit mysterious, don't you agree?

    first of all, why do the tables even have id columns, when the query clearly refers only to the names?

    are these values coming from a form text field? because if they came from dropdowns, they'd be using the id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The carrier and service names are being generated by external websites. We're making sure that we have all of them in our database. If a new service pops up for a given carrier, we want to store it in the database. We then use those ids to reference the carrier and service against other shipping information. We use the ids in our drop downs and to relate the carriers and services to other information in our database.

    The idea was that we would search the carrier and service simultaneously and then if the service wasn't found (null result), we would add the service to the database.

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by whitemank View Post
    The idea was that we would search the carrier and service simultaneously and then if the service wasn't found (null result), we would add the service to the database.
    Just curious, how does it work the other way around? i.e. if a carrier no longer offers a specific service, how do you know you should delete it from your database? Or is that not needed?
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by whitemank View Post
    The idea was that we would search the carrier and service simultaneously and then if the service wasn't found (null result), we would add the service to the database.
    thanks, that makes sense

    did you know you don't have to do the SELECT at all?

    just use INSERT IGNORE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We don't care if a service is no longer offered since it is just creating a snapshot in time where it is offered.

    INSERT IGNORE sounds like an interesting way to handle it, although I actually need to know the carrier_id and service_id in order to insert them into another table in the same script.

    At this point, I think I'll run the LEFT OUTER JOIN you helped me with and in the rare case that it comes back empty, I'll do two additional queries to see whether the carrier exists, and then whether the service exists.


Bookmarks

Posting Permissions

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