User's Guide
   PART 1. Working with Databases
     CHAPTER 6. Joins: Retrieving Data from Several Tables       

Joining more than two tables

To carry out many queries, you need you will need to join more than two tables. Here, you have two options at your disposal.

The first method to join multiple tables. The following command answers the question "What items were are listed on order number 2015?"

SELECT product.name, size, sales_order_items.quantity
FROM sales_order
   KEY JOIN sales_order_items
   KEY JOIN product
WHERE sales_order.id = 2015

id	name	size	quantity
300	Tee Shirt	Small	24
301	Tee Shirt	Medium	24
302	Tee Shirt	One size fits all	24
700	Shorts	Medium	24

When you want to join a number of tables sequentially, the above syntax makes a lot of sense. However, sometimes you need to join a single table to several others that surround it.

  Star joins

Some joins must join a single table to several others around it. This type of join is called a star join.

As an example, create a list the names of the customers that have placed orders with Rollin Overbey.

SELECT c.fname, c.lname, o.order_date
FROM sales_order AS o KEY JOIN customer AS c,
   sales_order AS o KEY JOIN employee AS e
WHERE e.emp_fname = 'Rollin' AND e.emp_lname = 'Overbey'
ORDER BY o.order_date

Notice that one of the tables in the FROM clause, employee, does not contribute any columns to the results. Nor do any of the columns that are joined—customer id and employee id—appear in the results. Nonetheless, this join is possible only by using the employee table.

fname	lname	order_date
Tommie	Wooten	1993-01-03
Michael	Agliori	1993-01-08
Salton	Pepper	1993-01-17
Tommie	Wooten	1993-01-23
Michael	Agliori	1993-01-24

The following statement uses a star join around the sales_order table. The result is a list that shows all the customers and the total quantity of each type of product that they have ordered. Some customers have not placed orders, so the other values for these customers are NULL. In addition, it shows the name of the manager of the sales person through whom they placed the orders.

SELECT c.fname, p.name, SUM(i.quantity), m.emp_fname
FROM   sales_order o
         KEY LEFT OUTER JOIN sales_order_items i
         KEY LEFT OUTER JOIN product p,
      sales_order o
         KEY RIGHT OUTER JOIN customer c,
      sales_order o
         KEY LEFT OUTER JOIN employee e
         LEFT OUTER JOIN employee m
            ON e.manager_id = m.emp_id
WHERE c.state = 'CA'
GROUP BY c.fname, p.name, m.emp_fname
ORDER BY SUM(i.quantity) DESC, c.fname

Note the following details of this statement:

• The join centers on the customer table.

• The keyword AS is optional and has been omitted.

• All joins must be outer joins to keep the outer join with the customer table includes null values.

• The condition e.manager_id = m.emp_id must be placed in the ON phrase instead of the WHERE clause. The result of this statement would been inner join if this condition moved into the WHERE clause.

The statement produces the results shown in the table, below.

fname	name	SUM(i.quantity)	emp_fname
Harry	(NULL)	(NULL)	(NULL)
Jane	(NULL)	(NULL)	(NULL)
Philipe	(NULL)	(NULL)	(NULL)
Sheng	Baseball Cap	240	Moira
Laura	Tee Shirt	192	Moira
Moe	Tee Shirt	192	Moira
Leilani	Sweatshirt	132	Moira
Almen	Baseball Cap	108	Moira