[thelist] sql cross table exclusion??
Michael Mell
mike at nthwave.net
Wed Aug 28 11:14:00 CDT 2002
Is there a clean way to do this in sql (MySQL)?
This is probably some classic sql problem that I don't know about.
I want to select all the users that do not own a Ford automobile.
I have a user table, a car table, and a userCar table:
++++++
table user -- holds all the users
userID
name
table car -- holds all the cars
carID
carType
table userCar -- associates a user with a car. Users may own more than
one car.
userID
carID
++++++
The data might look like so:
+++++++++
table user
userID : 001
name : Mike
userID : 002
name : John
userID : 003
name : Cynthia
table car
carID : 001
carType : Porsche
carID : 002
carType : Ford
carID : 003
carType : Subaru
table userCar
userID : 001
carID : 002
userID : 001
carID : 003
userID : 002
carID : 002
userID : 003
carID : 001
+++++++++
So, you see, Mike owns a Ford and a Subaru, but no Porsche.
John owns only a Ford. And Cynthia, the dear girl, owns only a Porsche.
Here's the problem:
How can I select all users who do not own a Ford or a Porsche?
if I do
+++++++++
SELECT userID FROM user, userCar
WHERE user.userID=userCar.userID and userCar.carID not in (001, 002);
+++++++++
I will obtain a result containing Mike and John.
"Mike" should not show up in my desired results, because he owns a Ford,
but he does show up because he has another car which is not a Ford.
Please let me know the magic sql formula to obtain my desired result.
many thanks.
m
--
mike[at]nthwave.net
More information about the thelist
mailing list