>I want to find all A's such that
>they have exactly B's 1 and 2
>SELECT A.ID, group_concat(BID ORDER BY BID) as Bs
>FROM A INNER JOIN AhasB ON A.ID=AID
>GROUP BY A.ID
>HAVING Bs='1,2'
Why the join? Doesn't your ahasb bridge table already incorporate the
join logic? If your requirement is to retrieve all aid's with exactly
one instance of bid=1, exactly one with bid=2, and no other bid's, why
not just ...
SELECT aid,GROUP_CONCAT(bid) AS list
FROM ahasb
GROUP BY aid
HAVING list='1,2';
PB
-----
James Northcott / Chief Systems wrote:
> Hello,
>
> I'm having a conceptual issue with many-to-many relations. I have the
> following structure:
>
> Table A
> ID (int primary key)
> ... descriptive columns ...
>
> Table B
> ID (int primary key)
> ... descriptive columns ...
>
> Table AhasB
> AID (references A.ID)
> BID (references B.ID)
>
> So, each A can have any number of B's, and each B can be had by any
> number of A's. I want to find all A's such that they have exactly B's
> 1 and 2. So far, the only working solution I have looks like:
>
> SELECT A.ID, group_concat(BID ORDER BY BID) as Bs
> FROM A INNER JOIN AhasB ON A.ID=AID
> GROUP BY A.ID
> HAVING Bs='1,2'
>
> This does work fine, but it seems very clunky - in particular, it's
> annoying to have to always remember to add the group_concat to the
> SELECT clause so that I can filter based on it in the HAVING clause,
> and it also doesn't scale particularly well, since HAVING isn't
> applied until the final stage of the query, so many rows are included
> in the result set that it would seem I ought to be able to filter
> earlier.
>
> Any ideas on how I can do this better/more efficiently? Also, does
> anybody have a name for what I'm trying to do? I'm finding it hard to
> even Google for information, since a can't seem to describe what I
> want concisely enough for a search.
>
> Thanks in advance,
>
> James
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.14.18/554 - Release Date: 11/27/2006
