Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 80

Does Not Work with
Microsoft access
aNSI-89 SQL

This query fails in Microsoft Access ANSI-89
SQL for the same reason previously described
on page 72.

Solution: See the solution described in the “Does
Not Work with Microsoft Access ANSI-89 SQL” box on page 72. The correct Microsoft
Access ANSI-89 SQL statement for this query is:

/* *** SQL-Query-CH02-50-Access *** */
SELECT Buyer, Count(*) AS NumberSold
FROM SKU_DATA
WHERE SKU IN

(SELECT SKU

FROM ORDER_ITEM

WHERE OrderNumber IN

(SELECT OrderNumber

FROM RETAIL_ORDER

WHERE OrderMonth='January'

AND OrderYear=2011))

GROUP BY Buyer

ORDER BY Count(*) DESC;

Querying Multiple Tables with Joins
Subqueries are very powerful, but they do have a serious limitation. The selected data can only
come from the top-level table. We cannot use a subquery to obtain data that arise from more
than one table. To do so, we must use a join instead.
The SQL join operator is used to combine two or more tables by concatenating (sticking
together) the rows of one table with the rows of another table. Consider how we might com-
bine the data in the RETAIL_ORDER and ORDER_ITEM tables. We can concatenate the rows
of one table with the rows of the second table with the following SQL statement, where we
simply list the names of the tables we want to combine:

/* *** SQL-Query-CH02-51 *** */
SELECT *
FROM RETAIL_ORDER, ORDER_ITEM;
This statement will just stick every row of one table together with every row of the second
table. For the data in Figure 2-5, the result is: