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]
