From: Peter Brawley Date: November 28 2006 3:53pm Subject: Re: Many-Many relation, matching all List-Archive: http://lists.mysql.com/mysql/203588 Message-Id: <456C5B7D.50906@earthlink.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit James Northcott / Chief Systems wrote: >SELECT AID >FROM AhasB WHERE BID in (1,2) >GROUP BY AID >HAVING count(BID) =2 Not quite, since that will catch aid's with two bid=1 rows or bid=2 rows: SELECT * FROM t; +------+------+ | i | j | +------+------+ | 1 | 4 | | 1 | 5 | | 3 | 5 | | 3 | 5 | +------+------+ SELECT i FROM t WHERE j in (4,5) GROUP BY i HAVING count(j) =2; +------+ | i | +------+ | 1 | | 3 | +------+ SELECT i,GROUP_CONCAT(j) AS list FROM t GROUP BY i HAVING list='4,5'; +------+------+ | i | list | +------+------+ | 1 | 4,5 | +------+------+ PB ----- > Peter Brawley wrote: >> >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 >> > I actually need some of the other columns from A, but you're correct, > this does work. I did discover though that the ORDER BY in the > group_concat is important, since MySQL doesn't always pick the same > order for the list. > > ddevaudreuil@stripped wrote: >> I think this will work: >> >> SELECT AID >> FROM AhasB WHERE BID in (1,2) >> GROUP BY AID >> HAVING count(BID) =2 >> >> >> Donna >> > > Thank you, this is actually very helpful. The where clause uses the > index I have in the table to screen out many more rows early in the > query, and it also very nicely avoids the string compare on the > group_concat. I also wasn't aware that you could use an aggregate > function in the HAVING clause without it appearing in the SELECT clause. > > Thanks again, this solves the problem quite elegantly, and I probably > never would have thought of it. > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.14.19/555 - Release Date: 11/27/2006