List:General Discussion« Previous MessageNext Message »
From:ddevaudreuil Date:November 28 2006 11:39am
Subject:Re: Many-Many relation, matching all
View as plain text  
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 <james@stripped> 
11/27/2006 04:35 PM

To
mysql@stripped
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/mysql?unsub=1


-- 
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.


Thread
Many-Many relation, matching allJames Northcott / Chief Systems27 Nov
  • Re: Many-Many relation, matching allPeter Brawley28 Nov
    • Re: Many-Many relation, matching allJames Northcott / Chief Systems28 Nov
      • Re: Many-Many relation, matching allPeter Brawley28 Nov
        • Re: Many-Many relation, matching allddevaudreuil28 Nov
  • Re: Many-Many relation, matching allddevaudreuil28 Nov