Collection Contents Index CHAPTER 17.  Introduction to Subqueries A simple subquery pdf/chap17.pdf

First Guide to SQL Anywhere Studio
   PART 3. Basic SQL
     CHAPTER 17. Introduction to Subqueries       

Preparing to use subqueries


Sometimes it is useful to use the results of one statement as part of another statement.

Example 1 

For example, suppose that you need a list of order items for products that are low in stock.

You can look up the products for which there are less than 20 items in stock in the product table.

  To list all products for which there are less than 20 items in stock:
  1. Type the following:

    SELECT id, description, quantity
    FROM product
    WHERE quantity < 20

id

description

quantity

401

Wool cap

12

This query shows that only wool caps are low in stock.

Example 2 

You can list all the order items for wool caps with the following query:

  To list all orders for wool caps, most recent first:
  1. Type the following:

    SELECT *
    FROM sales_order_items
    WHERE prod_id = 401
    ORDER BY ship_date DESC

id

line_id

prod_id

quantity

ship_date

2082

1

401

48

1994-07-09

2053

1

401

60

1994-06-30

2125

2

401

36

1994-06-28

2027

1

401

12

1994-06-17

2062

1

401

36

1994-06-17

This two-step process of identifying items low in stock and identifying orders for those items can be combined into a single query using subqueries.


Collection Contents Index CHAPTER 17.  Introduction to Subqueries A simple subquery pdf/chap17.pdf