[thelist] sql cross table exclusion??

[rudy]

> 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/