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