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]
