Three- and Four-Table Joins

Figure  demonstrates a three-table join.  

        test=> SELECT customer.name, employee.name
        test-> FROM   salesorder, customer, employee
        test-> WHERE  salesorder.customer_id = customer.customer_id AND
        test->        salesorder.employee_id = employee.employee_id AND
        test->        salesorder.order_id = 14673;
                      name              |              name              
        --------------------------------+--------------------------------
         Fleer Gearworks, Inc.          | Lee Meyers                    
        (1 row)  

In this example, the first printed column is the customer name, and the second column is the employee name. Both columns are labeled name. You could use AS to give the columns unique labels. Figure  shows a four-table join, using AS to make each column label unique.   

        test=> SELECT customer.name AS customer_name, 
        test->        employee.name AS employee_name, 
        test->        part.name AS part_name
        test-> FROM   salesorder, customer, employee, part
        test-> WHERE  salesorder.customer_id = customer.customer_id AND
        test->        salesorder.employee_id = employee.employee_id AND
        test->        salesorder.part_id = part.part_id AND
        test->        salesorder.order_id = 14673;
                customer_name         |      employee_name       |      part_name
        ------------------------------+--------------------------+---------------------
         Fleer Gearworks, Inc.        | Lee Meyers               | Garage Door Spring
        (1 row)  

The four-table join matches the arrows in Figure , with the arrows of the salesorder table pointing to the other three tables.

Joins can also be performed among tables that are only indirectly related. Suppose you wish to find employees who have taken orders for each customer. Figure  shows such a query.  

        test=> SELECT DISTINCT customer.name, employee.name
        test-> FROM   customer, employee, salesorder
        test-> WHERE  customer.customer_id = salesorder.customer_id and
        test->        salesorder.employee_id = employee.employee_id
        test-> ORDER BY customer.name, employee.name;
                      name              |              name              
        --------------------------------+--------------------------------
         Fleer Gearworks, Inc.          | Lee Meyers                    
        (1 row) 
         
        test=> SELECT DISTINCT customer.name, employee.name, COUNT(*)
        test-> FROM   customer, employee, salesorder
        test-> WHERE  customer.customer_id = salesorder.customer_id and
        test->        salesorder.employee_id = employee.employee_id
        test-> GROUP BY customer.name, employee.name 
        test-> ORDER BY customer.name, employee.name;
                      name              |              name              | count 
        --------------------------------+--------------------------------+-------
         Fleer Gearworks, Inc.          | Lee Meyers                     |     1
        (1 row)  

Notice that this query displays just the customer and employee tables. The salesorder table is used to join the two tables but does not appear in the result. The DISTINCT  keyword is used because multiple orders taken by the same employee for the same customer would make that employee appear more than once, which was not desired. The second query uses an aggregate to return a count for each unique customer/employee pair.  

Until now, we have used only a single row in each table. As an exercise, add more customer, employee, and part rows, and add salesorder rows that join to these new entries. You can use Figure  as an example. Choose any unique identification numbers you like, then try the queries already shown in this chapter with your new data.