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

```