[thelist] sql cross table exclusion??
rudy
r937 at interlog.com
Wed Aug 28 13:21:08 CDT 2002
> 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
rudy
http://rudy.ca/
More information about the thelist
mailing list