Home arrow MySQL arrow Page 5 - Using Subqueries In MySQL (part 1)

Branching Out - 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.

TABLE OF CONTENTS:
  1. Using Subqueries In MySQL (part 1)
  2. Sub-Zero Code
  3. Turning The Tables
  4. Back To Basics
  5. Branching Out
  6. Having Your Code, And Eating It Too
  7. Apples And Oranges
By: RK Harigopal, (c) Melonfire
Rating: starstarstarstarstar / 14
July 24, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By RK Harigopal, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: