HomeMySQL Page 6 - Using Subqueries In MySQL (part 1)
Having Your Code, And Eating It Too - MySQL
One of the most powerful new features in MySQL 4.1 is subqueries, which make it possible to nest multiple SQL queries within each other. This introductory segment of a two-part tutorial demonstrates how to use this new feature to build sophisticated data retrieval queries, illustrating (among other things) how to use subqueries in WHERE and HAVING clauses, with comparison and logical operators, with aggregate functions, and with inner, left, right and self joins.
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)
