SQL: Get list of non-coupled items using MySQL 4
I'm not that good in SQL and I've come across a problem I don't know how to solve. I've read and re-read parts of a book about SQL (O'Reilly's Learning SQL) which I hoped would contain the inform开发者_如何学Cation I needed but I haven't found it.
My problem is the following. I'll use a simplified example to make it easier to discuss.
I've got three tables, car, rim and the combination of the two: carRim.
car
carId
description
rim
rimId
description
carRim
carRimId
carId
rimId
price
In the table carRim I have an extra attribute of price, because the price of a rim is potentially different for every type of car. A constraint I have is that every type of rim should only be coupled once to every type of car. So all combinations of car-rim should be unique.
If I want to add a rim to a car I need a list of rims that are not yet coupled to that car. For this I think I need the rim table and the carRim table respectively for the total list of rims and the list of carRims that are already coupled to the car I want to add a rim to.
I've written the (simple) query to make the list of rims that are coupled to a specific car, in the following example the car with carId 9.
SELECT
*
FROM
rims
INNER JOIN
carRims
ON
carRims.rimId = rim.rimId
WHERE
carRims.carId = 9
But now I need the list of rims that are not yet coupled to a specific car. The problem is that if I do a LEFT OUTER JOIN the list I get is "tainted" with couplings of rims to other cars, so the filter condition of "WHERE carRims.carId IS NULL" does not work.
SELECT
*
FROM
rims
LEFT OUTER JOIN
carRims
ON
carRims.rimId = rim.rimId
WHERE
carRims.carId IS NULL
Another challenge is that I can't use any syntax new to MySQL 5, like subqueries, because my client is using MySQL 4 and can't upgrade at this time.
Can a query for this problem be written in MySQL 4, I suspect it can.
Thanks!
SELECT *
FROM rims r
WHERE NOT EXISTS
(
SELECT NULL
FROM carRims cr
WHERE cr.rimId = r.RimId
AND cr.carID = 9
)
Update:
To rewrite the NOT EXISTS
into a LEFT JOIN / IS NULL
, you'll need to put all conditions into the ON
clause of the join:
SELECT r.*
FROM rims r
LEFT OUTER JOIN
carRims cr
ON cr.rimId = r.rimId
AND cr.crID = 9
WHERE cr.carId IS NULL
You can put extra conditions in a left outer join ON
expression. What you want is a row from carRims
that matches your rims
row, and belongs to car number 9, right?
SELECT
*
FROM
rims
LEFT OUTER JOIN
carRims
ON
carRims.rimId = rim.rimId
AND carRims.carId = 9
WHERE
carRims.carId IS NULL
If there are other carRims
for other cars, they'll be filtered out by the extra condition in the ON
clause.
Re your questions about where to put a condition, in the JOIN
or in the WHERE
clause:
For outer joins, it matters where you put the comparison. The condition in a JOIN clause is for testing whether rows in one table match rows in the other table. What we do with the rows from the respective tables after we test the match depends on the type of join.
For an outer join, we want rows from rims
even if there's no matching row in carRims
. What if we were to put the carID=9 condition in the WHERE
clause?
FROM rims r LEFT OUTER JOIN carRims c ON r.rimId = c.rimID
WHERE c.carID = 9
Here's what happens: the outer join returns all rows from rims
, with rows from carRims
that match the rimID
and includes the rows with spurious carID
values. Only if none of the cars match a given rim does it use NULL for the c.*
columns.
But then the WHERE
clause eliminates all the rows resulting from the join, unless carID
is the value 9. That means it also eliminates where carID
is NULL, that is it eliminates rows for any rim matches none of the cars. Therefore the result becomes equivalent to the result of an INNER JOIN
.
So we need to exclude rows from carRims
with the wrong carID before the rows are joined to rims
.
FROM rims r LEFT OUTER JOIN carRims c ON r.rimId = c.rimID AND c.carID = 9
It's a condition on what rows from that individual table can match rows in the other joined table.
Many books say you can freely mix conditions between the ON
clause and the WHERE
clause. But this isn't true in all cases. It works for INNER JOIN
, because the end result is the same either way. It also works for a condition that applies only to the left table in a left join, for example:
FROM rims r LEFT OUTER JOIN carRims c ON r.rimId = c.rimID AND r.make = 'ABC Rims'
FROM rims r LEFT OUTER JOIN carRims c ON r.rimId = c.rimID
WHERE r.make = 'ABC Rims'
For a condition on the right table in a left outer join, it does matter where you put the condition.
One more comment on something you said:
A constraint I have is that every type of rim should only be coupled once to every type of car. So all combinations of car-rim should be unique.
So have you declared a UNIQUE
constraint on carRims(carId,rimId)
?
(I needed some more room to elaborate, a comment did not have enough characters)
@Bill Karwin The query you've provided is the correct answer to my problem. Thanks!
What I wanted was a list of rows (rims) that are not connected to a specific car.
In the "Learning SQL" book I read that the location of filter and join conditions in the query did not matter. They gave an example of an inner join where there was 1 join condition (after the ON clause) and 1 filter condition (after the WHERE clause). They demonstrated that whether you put those two conditions after the ON clause or after the WHERE clause effectively did not matter.
With that in mind I don't understand a part of your solution. If the condition
carRims.carId = 9
and the condition
carRims.carId IS NULL
are both present, doesn't that result in an empty resultset? Because no field can be 9 and NULL simultaneously right?
My suspicion then is: the conditions in the JOIN clause are location-bound, and you can't just put them in the WHERE clause.
Is this correct?
精彩评论