Yes, it's true that the query won't work if you have duplicate aid,bid rows. I probably shouldn't have assumed that there would be a PK or unique constraint on aid,bid. So if that isn't the case, you can add a distinct:
SELECT AID FROM AhasB WHERE BID in (1,2) GROUP BY AID HAVING count(distinct BID) =2 Donna Peter Brawley <[EMAIL PROTECTED]> 11/28/2006 10:53 AM Please respond to [EMAIL PROTECTED] To James Northcott / Chief Systems <[EMAIL PROTECTED]>, "mysql@lists.mysql.com" <mysql@lists.mysql.com> cc Subject Re: Many-Many relation, matching all 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. > > [EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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.
