From: Peter Brawley
Date: November 28 2006 4:04am
Subject: Re: Many-Many relation, matching all
List-Archive: http://lists.mysql.com/mysql/203566
Message-Id: <456BB560.8050702@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 >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