Branching Out - MySQL

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.