Another many to many relationship SQL question

["Joe Kaiping" <kaiping@xxxxxxxxxxxx>]

Hi list,

I have another SQL question concerning many to many relations.

Does anyone know of a way to build a SELECT that would return individuals
that _don't_ have a certain many to many relationship?

e.g.

ind table
---------
id = 4, name = Joe
id = 5, name = Lori

bus_ctr table
-------------
id = 1, name = downtown mall
id = 2, name = sunshine mall
id = 3, name = express shop

mm_ind_bus_ctr table
--------------------
id = 2, ind_id = 4, bus_ctr_id = 1
id = 3, ind_id = 4, bus_ctr_id = 2
id = 4, ind_id = 4, bus_ctr_id = 3
id = 5, ind_id = 5, bus_ctr_id = 1
id = 6, ind_id = 5, bus_ctr_id = 2

Not a problem to retrieve a list of only individuals associated with all the
following bus. ctrs: 1, 2, _and_ 3.  (i.e. only "Joe" in this example)  The
following SELECT shows how. (Thanks again, Benjamin!):

SELECT ind.id,ind.Name
  FROM ind, mm_ind_bus_ctr mm0, mm_ind_bus_ctr mm1, mm_ind_bus_ctr mm2
 WHERE mm0.bus_ctr_id='1' AND ind.id=mm0.ind_id
   AND mm1.bus_ctr_id='2' AND ind.id=mm1.ind_id
   AND mm2.bus_ctr_id='3' AND ind.id=mm2.ind_id

Now I'm having trouble creating a SELECT to look for contacts that _aren't_
associated with a certain bus_ctr (e.g. All individuals that don't have a
many to many relationship with bus_ctr.id=3 (express shop))  The select:

SELECT DISTINCT ind.id,ind.Name
  FROM ind, mm_ind_bus_ctr mm0
 WHERE mm0.bus_ctr<>'3' AND ind.id=mm0.ind

returns _both_ "Lori" and "Joe".  And I only want "Lori" to be returned from
the SELECT.  Is it possible to create a SELECT that will do this?

Thanks,
-Joe


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread87404@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php