List:General Discussion« Previous MessageNext Message »
From:ddevaudreuil Date:November 28 2006 4:14pm
Subject:Re: Many-Many relation, matching all
View as plain text  
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 <peter.brawley@stripped> 
11/28/2006 10:53 AM
Please respond to
peter.brawley@stripped


To
James Northcott / Chief Systems <james@stripped>, 
"mysql@stripped" <mysql@stripped>
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.
>
> 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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/mysql?unsub=1


-- 
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.


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