List:General Discussion« Previous MessageNext Message »
From:James Northcott / Chief Systems Date:November 27 2006 9:35pm
Subject:Many-Many relation, matching all
View as plain text  
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
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