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

Turning The Tables - MySQL

In this concluding segment of our MySQL subquery tutorial, find out how to do more with subqueries, including check for matching values with the IN operator, check for valid result sets with the EXISTS operator, derive new "virtual" tables for use in the FROM clause of outer queries, and UPDATE and DELETE records selectively.

TABLE OF CONTENTS:
  1. Using Subqueries In MySQL (part 2)
  2. Total Recall
  3. In And Out
  4. A Solitary Existence
  5. Turning The Tables
  6. Show Me The Money
  7. Adjusting For Inflation
  8. A New Broom
  9. Cleaning Up
By: RK Harigopal, (c) Melonfire
Rating: starstarstarstarstar / 16
July 31, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

You can also use the results generated by a subquery as a table in the FROM clause of an enclosing SELECT statement. In order to illustrate, let's say I want to know the average number of services each branch office has. Therefore, the first thing I need to do is group the branches together and count the total number of services each one has,




mysql> SELECT bid, COUNT(sid) AS stotal FROM branches_services GROUP BY
mysql> bid;
+------+--------+
| bid | stotal |
+------+--------+
| 1011 | 4 |
| 1012 | 1 |
| 1013 | 1 |
| 1031 | 3 |
| 1032 | 1 |
| 1033 | 1 |
| 1041 | 2 |
| 1042 | 2 |
| 1101 | 1 |
+------+--------+
9 rows in set (0.17 sec)

and then calculate the rounded-off average of the per-branch totals. Or, in a subquery,


mysql> SELECT AVG(z.stotal) FROM (SELECT bid, COUNT(sid) AS stotal FROM
branches_services GROUP BY bid) AS z;
+---------------+
| AVG(z.stotal) |
+---------------+
| 1.7778 |
+---------------+
1 row in set (0.21 sec)

Thus, the table of results generated by the inner query is used in the FROM clause of the outer query. You can convert the average number above into an integer with the CEILING() function if you like.

Note that when using subquery results in this manner, the result table generated by the inner query must be first aliased to a table name, or else MySQL will now know how to refer to columns within it. Look what happens when I re-run the query above without the table alias:


mysql> SELECT AVG(stotal) FROM (SELECT bid, COUNT(bid) AS stotal FROM
branches_services GROUP BY bid);
ERROR 1246: Every derived table must have it's own alias

Now, let's take it a step further. What if I need to list the branches where the number of services is above this average?


mysql> SELECT bid FROM branches_services GROUP BY bid HAVING COUNT(sid)
mysql> >
1.7778;
+------+
| bid |
+------+
| 1011 |
| 1031 |
| 1041 |
| 1042 |
+------+
4 rows in set (0.28 sec)

To make it truly dynamic, you should combine the two queries above into the following complex query:


mysql> SELECT bid FROM branches_services GROUP BY bid HAVING COUNT(sid)
mysql> >
(SELECT AVG(z.stotal) FROM (SELECT bid, COUNT(bid) AS stotal FROM branches_services GROUP BY bid) AS z);
+------+
| bid |
+------+
| 1011 |
| 1031 |
| 1041 |
| 1042 |
+------+
4 rows in set (0.28 sec)

Wanna make it really complicated? Add another query (and a join) around it to get the customer names corresponding to those branches.


mysql> SELECT DISTINCT cname FROM clients, branches, (SELECT bid FROM
branches_services GROUP BY bid HAVING COUNT(sid) > (SELECT AVG(z.stotal) FROM (SELECT bid, COUNT(bid) AS stotal FROM branches_services GROUP BY bid) AS z)) AS x WHERE clients.cid = branches.cid AND branches.bid = x.bid;
+------------------+
| cname |
+------------------+
| JV Real Estate |
| DMW Trading |
| Rabbit Foods Inc |
+------------------+
3 rows in set (0.33 sec)

Whew!



 
 
>>> 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: