[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