[thelist] Re: sql cross table exclusion??

[Michael Mell]

Ack! I made a mistake in the data for my example.
User 002 (John) should own a carType 003 (Subaru)


Michael Mell wrote:

> 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

--

mike[at]nthwave.net