Using Subqueries In MySQL (part 1) - Having Your Code, And Eating It Too (
Page 6 of 7 )
You can use a subquery in a HAVING clause as well, and thereby use it as a filter for the groups created in the parent query. I'll demonstrate by finding out which sites are using more than 50% of all available services.
mysql> SELECT bid FROM branches_services GROUP BY bid HAVING COUNT(sid)
mysql> >
(SELECT COUNT(*) FROM services)/2;
+------+
| bid |
+------+
| 1011 |
+------+
1 row in set (0.22 sec)
You can add a fast inner join to get the branch name and customer name as well if you like:
mysql> SELECT c.cid, c.cname, b.bid, b.bdesc FROM clients AS c, branches
mysql> AS
b, branches_services AS bs WHERE c.cid = b.cid AND b.bid = bs.bid GROUP BY bs.bid
HAVING COUNT(bs.sid) > (SELECT COUNT(*) FROM services)/2;
+-----+----------------+------+--------------+
| cid | cname | bid | bdesc |
+-----+----------------+------+--------------+
| 101 | JV Real Estate | 1011 | Corporate HQ |
+-----+----------------+------+--------------+
1 row in set (0.28 sec)
Is it possible to get a list of branches using all available services? Sure!
mysql> SELECT branches.bid, COUNT(sid) FROM branches, branches_services
WHERE branches.bid = branches_services.bid GROUP BY branches.bid HAVING
COUNT(sid) = (SELECT COUNT(*) FROM services);
Empty set (0.04 sec)
And that's quite right - if you look at the raw data, you'll see that there are no individual branch offices using all available services. Maybe if I went up the totem pole a little and checked to see if there were any clients using all available services across their branch offices...
mysql> SELECT clients.cname FROM clients, branches, branches_services
mysql> WHERE
branches.bid = branches_services.bid AND branches.cid = clients.cid GROUP BY
clients.cid HAVING COUNT(sid) = (SELECT COUNT(*) FROM services);
+----------------+
| cname |
+----------------+
| JV Real Estate |
+----------------+
1 row in set (0.01 sec)
