Additional Join Possibilities

So far, all of our example joins have involved the salesorder table in some form. Suppose we want to assign an employee to manage each customer account. If we add an employee_id column to the customer table, the column could store the identification number of the employee assigned to manage the customer's account. Figure  shows how to perform a join between the customer and employee tables.  

        SELECT employee.name
        FROM   customer, employee
        WHERE  customer.employee_id = employee.employee_id AND
               customer.customer_id = 648; 
         
        SELECT customer.name
        FROM   customer, employee
        WHERE  customer.employee_id = employee.employee_id AND
               employee.employee_id = 24
        ORDER BY customer.name;  

The first query finds the employee name assigned to manage customer number 648. The second query shows the customers managed by employee 24. Notice that the salesorder table is not involved in these queries.

Suppose you want to assign an employee to be responsible for answering detailed questions about parts. You would then add an employee_id column to the part table, place valid employee identifiers in the column, and perform queries similar to those in Figure . Adding columns to existing tables is covered in Section .  

        -- find the employee assigned to part number 153
        SELECT employee.name
        FROM   part, employee  
        WHERE  part.employee_id = employee.employee_id AND    
               part.part_id = 153; 
         
        -- find the parts assigned to employee 24
        SELECT part.name
        FROM   part, employee  
        WHERE  part.employee_id = employee.employee_id AND    
               employee.employee_id = 24
        ORDER BY name;  

In some cases, a join could be performed with the state column. For example, to check state mailing codes for validity, a statecode table could be created with all valid state codes. ^10.3 An application could check the state code entered by the user and report an error if it is not in the statecode table. Another example would be the need to print the full state name in queries. State names could be stored in a separate table and joined when the full state name is desired. Figure  shows an example of such a statename table.  

        test=> CREATE TABLE statename (code CHAR(2),
        test(>                          name  CHAR(30) 
        test(> );
        CREATE
        test=> INSERT INTO statename VALUES ('AL', 'Alabama');
        INSERT 20629 1
         
         
        test=> SELECT statename.name AS customer_statename
        test-> FROM   customer, statename
        test-> WHERE  customer.customer_id = 648 AND
        test->        customer.state = statename.code;  

Thus we have two more uses for additional tables:

• Check codes against a list of valid values--that is, allow only valid state codes
• Store code descriptions--that is, state code and state name