>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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
