Using Subqueries In MySQL (part 1) - Branching Out
(Page 5 of 7 )
Subqueries are usually preceded by a conditional WHERE clause, which can contain any of the following comparison and logical operators:
Operator What It Means
---------------------------------------------
= values are equal
<> values are unequal
<= value on left is less than or equal to value on right
>= value on left is greater than or equal to value on right
< value on left is less than value on right
> value on left is greater than value on right
BETWEEN value on left lies between values on right
NOT logical NOT
AND logical AND
OR logical OR
In order to demonstrate, let's say I wanted a list of all those customers with exactly two branch offices. First, I need to figure out a way to obtain the number of branch offices per customer,
mysql> SELECT cid, COUNT(bid) FROM branches GROUP BY cid;
+-----+------------+
| cid | count(bid) |
+-----+------------+
| 101 | 3 |
| 103 | 3 |
| 104 | 2 |
| 110 | 1 |
+-----+------------+
4 rows in set (0.27 sec)
and then filter out those with just two offices with a HAVING clause,
mysql> SELECT cid, COUNT(bid) FROM branches GROUP BY cid HAVING
mysql> COUNT(bid)
= 2;
+-----+------------+
| cid | count(bid) |
+-----+------------+
| 104 | 2 |
+-----+------------+
1 row in set (0.16 sec)
and then hand the client ID over to the "clients" table in order to get the client name.
mysql> SELECT cname FROM clients WHERE cid = 104;
+------------------+
| cname |
+------------------+
| Rabbit Foods Inc |
+------------------+
1 row in set (0.22 sec)
The following subquery will take care of the three steps above for me:
mysql> SELECT cname FROM clients WHERE cid = (SELECT cid FROM branches
GROUP BY cid HAVING COUNT(bid) = 2);
+------------------+
| cname |
+------------------+
| Rabbit Foods Inc |
+------------------+
1 row in set (0.28 sec)
In this case, the inner query is executed first - this query takes care of grouping the branches by customer ID and counting the number of records (branch offices) in each group. Those customers which have exactly two branch offices can easily filtered out with a HAVING clause, and the corresponding customer IDs returned to the main query, which then maps the IDs into the customers table and returns the corresponding customer name.
How about selecting all those customers using the service with the maximum service fee?
mysql> SELECT cname, bdesc FROM clients, branches, branches_services,
services WHERE services.sid = branches_services.sid AND clients.cid = branches.cid
AND branches.bid = branches_services.bid AND sfee = (SELECT
MAX(sfee) FROM services);
+----------------+--------------------------------+
| cname | bdesc |
+----------------+--------------------------------+
| JV Real Estate | Customer Grievances Department |
+----------------+--------------------------------+
1 row in set (0.01 sec)
Next up, HAVING clauses.
Next: Having Your Code, And Eating It Too >>
More MySQL Articles
More By RK Harigopal, (c) Melonfire
