Having Your Code, And Eating It Too - MySQL

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)