[thelist] Re: sql cross table exclusion??
Michael Mell
mike at nthwave.net
Wed Aug 28 11:42:49 CDT 2002
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
More information about the thelist
mailing list