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'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).
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.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
