> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Benjamin
> Hi.
>
> On Mon, Oct 01, 2001 at 07:54:37PM -0700, [EMAIL PROTECTED] wrote:
> >
> > Hi there,
> >
> > I have a many-to-many relationship:
> >
> > ind table
> > ---------
> > id = 4, name = Joe
> > id = 5, name = Jane
> >
> > bus_ctr table
> > -------------
> > id = 1, name = downtown mall
> > id = 2, name = sunshine mall
> > id = 3, name = express shop
> >
> > mm_ind_bus_ctr table (many to many association table)
> > -----------------------------------------------------
> > id = 6, ind_id = 4, bus_ctr_id = 1
> > id = 7, ind_id = 4, bus_ctr_id = 2
> > id = 8, ind_id = 4, bus_ctr_id = 3
> > id = 9, ind_id = 5, bus_ctr_id = 1
> >
> > Is there a way to create a SELECT that returns only the
> people in the ind
> > table that are associated with bus_ctr.id=1, bus_ctr.id=2
> and bus_ctr.id=3?
> > In other words a SELECT that only returns the ind.id = 4
> (Joe) record?
>
> Do you want an "exact" match or an "at least" match, i.e. what if
>
> id = 10, ind_id = 4, bus_ctr_id = 5
>
> would be given. Does Joe still match?
>
I'm looking for an exact match, so if that record also existed in
mm_ind_bus_ctr, Joe should not match.
> > I've only been able to create a SELECT that would return both Jane
> > and Joe so far.
>
> If you have something, please post it, even if it isn't correct. It
> will give other a better idea of what's all about (if you also post
> the result and what you don't like about it).
>
I was going at it from the wrong direction as this provides the "at least"
match.
SELECT DISTINCT i.id, i.name
FROM ind i, mm_ind_bus_ctr mm
WHERE mm.bus_ctr_id IN (1,2,3)
AND i.id=mm.ind_id;
> The select will get a bit large, because you need several joins. In
> fact, you want to check for three relations and therefore needs as
> much tables to join with:
>
> SELECT i.id, i.name
> FROM ind i, mm_ind_bus_ctr bi1, mm_ind_bus_ctr bi2,
> mm_ind_bus_ctr bi3
> WHERE bi1.bus_ctr_id = 1 AND
> bi2.bus_ctr_id = 2 AND
> bi3.bus_ctr_id = 3 AND
> bi1.ind_id = i.id AND
> bi2.ind_id = i.id AND
> bi3.ind_id = i.id
>
> Untested, but should work.
>
It does work and is exactly what I was hoping for. I was missing the
multiple alias for a single table technique.
> Bye,
>
> Benjamin.
>
Thank you, Benjamin. I really appreciate your time and help!
-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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
