[thelist] sql cross table exclusion??

[j.cortmaior]

or:

select user.userid, user.name, usercar.cartype from user, usercar
	where usercar.userid = user.userid
	and usercar.cartype not in ('Ford', 'Porche');


No guarantees, but I bet that works.

--jon

___________________________________________________________________
  Jon Cortmaior | poor rabbit consulting | unix geek | web monkey
-------------------------------------------------------------------
"The man who does not read good books has no advantage over the man
 who cannot read them."  -- Mark Twain
___________________________________________________________________
        rabbit at poorrabbit.com http://www.poorrabbit.com

> Message: 5
> From: "rudy" <r937 at interlog.com>
> To: <thelist at lists.evolt.org>
> Subject: Re: [thelist] sql cross table exclusion??
> Date: Wed, 28 Aug 2002 14:16:27 -0400
> Reply-To: thelist at lists.evolt.org
>
> > Is there a clean way to do this in sql (MySQL)?
>
> hi michael
>
> that's actually two questions   ;o)
>
> you were right, it is a classic sql problem
>
> but mysql does not allow subqueries, so you cannot use the classical
> solution
>
> > How can I select all users who do not own a Ford or a Porsche?
>
>    select userid, name
>      from user xx
>     where not exists
>           ( select 1
>               from usercar
>             inner
>               join car
>                 on usercar.carID = car.carID
>              where usercar.userID = xx.userID
>                and cartype in ('Ford','Porsche')
>           )
>
> here's how it works in english --
>
> look at all the cars owned by each user and check each one to see if it's a
> 'Ford' or 'Porsche'
>
> if it is one of these, then this isn't a user you want
>
> the result is all the users who aren't users you didn't want
>
> ;o)
>
> or, in pseudo-sql --
>
> select the users who don't have a Ford or Porsche
>
> (astute readers will note that, for rudy, the pseudo-sql is a lot closer to
> both the original statement of the problem and the actual sql than the
> attempted english explanation of the correlated subselect)
>
> it's a good thing you specified "users who don't have X or Y" because if
> you had said "users who have neither X nor Y" the solution is totally
> different!!
>
> here's another way to do the "don't have X or Y" query, not as efficient as
> the "not exists" approach
>
> if you test this, please do be sure and let me know if it works --
>
>    select user.userID, name
>      from user
>    left outer
>      join usercar
>        on user.userID = usercar.userID
>    left outer
>      join car
>        on usercar.carID = car.carID
>       and cartype in ('Ford','Porsche')
>    group
>        by user.userID, name
>    having count(cartype) = 0
>
>