[thelist] sql cross table exclusion??
j.cortmaior
wirehead at poorrabbit.com
Wed Aug 28 21:52:01 CDT 2002
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
>
>
More information about the thelist
mailing list