Database Reference
In-Depth Information
3.
Retrieve the customer information for all the customer IDs returned in
the previous step.
Each of these steps can be executed as a separate query. By doing so, you use
the results returned by one
SELECT
statement to populate the
WHERE
clause of
the next
SELECT
statement.
You can also use subqueries to combine all three queries into one single
statement.
The first
SELECT
statement should be self-explanatory by now. It retrieves the
order_num
column for all order items with a
prod_id
of
TNT2
. The output
lists the two orders containing this item:
▼
Input
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';
▼
Output
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
The next step is to retrieve the customer IDs associated with orders
20005
and
20007
. Using the
IN
clause described in Chapter 7, “Advanced Data
Filtering,” you can create a
SELECT
statement as follows:
▼
Input
SELECT cust_id
FROM orders
WHERE order_num IN (20005,20007);
▼
Output
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
