I think this will work:
SELECT AID
FROM AhasB
WHERE BID in (1,2)
GROUP BY AID
HAVING count(BID) =2
Donna
James Northcott / Chief Systems <[EMAIL PROTECTED]>
11/27/2006 04:35 PM
To
mysql@lists.mysql.com
cc
Subject
Many-Many relation, matching all
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
CONFIDENTIALITY NOTICE:This email is intended solely for the person or
entity to which it is addressed and may contain confidential and/or
protected health information. Any duplication, dissemination, action
taken in reliance upon, or other use of this information by persons or
entities other than the intended recipient is prohibited and may violate
applicable laws. If this email has been received in error, please notify
the sender and delete the information from your system. The views
expressed in this email are those of the sender and may not necessarily
represent the views of IntelliCare.
