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?
And here is my failed attempt to solve this myself:
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'
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:
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
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:
SELECT
c.carrier_id
, cs.service_id
FROM api_carriers as c
LEFT OUTER JOIN api_carrier_services as cs
[B][COLOR="Red"]ON c.carrier_id=cs.carrier_id
AND cs.service_name='Next Day Air'[/COLOR][/B]
WHERE c.carrier_name='FedEx' AND
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.
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?
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.
