Home arrow MySQL arrow 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.

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

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)



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