Multiple Table Queries

1. Multiple Table Queries A Guide to SQL – Chapter 5

2. Instructional Objectives • Use joins to retrieve data from one than one table • Use IN and EXISTS operators to query multiple tables • Use a subquery within a subquery • ALL • ANY

3. Joining tables • Used when dealing with multiple tables (rather than writing lengthy subqueries) • Finds rows in two tables that have identical values in matching columns • Example: • List the number and name of each customer along with the number, last name, and first name of the sales rep who represents the customer. Sort the records by customer number.

4. Solution SELECT CustomerNum, CustomerName, Rep.RepNum, LastName, FirstName FROM Customer, Rep WHERE Customer.RepNum = Rep.RepNum ORDER BY CustomerNum;

5. Another example • Lists the number and name of each customer whose credit limit is $7,500 together with the number, last name, and first name of the sales rep who represents the customer

6. Solution SELECT Customer_Num, Customer_Name, Rep.Rep_Num, Last_Name, First_Name FROM Customer, Rep WHERE Customer.Rep_Num = Rep.Rep_Num AND Credit_Limit = 7500;

7. Example • For every part on order, list the order number, part number, part description, number of units ordered, quoted price, and unit price • Solution: SELECT Order_Num, Order_Line.Part_Num, Description, Num_Ordered, Quoted_Price, Price FROM Order_Line, Part WHERE Order_Line.Part_Num = Part.Part_Num;

8. Example • Find the description of each part included in order number 21610 • Solution: SELECT Description FROM Order_Line, Part WHERE Order_Line.Part_Num = Part.Part_Num AND Order_Num = ‘21610’;

9. Another way… IN Operator SELECT Description FROM Part WHERE Part_Num IN (SELECT Part_Num FROM Order_Line WHERE Order_Num = ‘21610’);

10. Your Turn • List all the number and dates of those orders that include a part located in warehouse 3

11. Solution

12. Alternate solution… subquerywithin a subquery • Find the order number and order date for each order that includes a part located in warehouse 3 • Solution: SELECT Order_Num, Order_Date FROM Orders WHERE Order_Num IN (SELECTOrder_Num FROM Order_Line WHERE Part_Num IN (SELECT Part_Num FROM Part WHERE Warehouse = ‘3’));

13. Exists • Can also be used to get data from multiple tables • Checks for existence of rows that satisfy some criterion • Example: • Find the order number and order date for each order that contains part number DR93

14. Solution:

15. Your turn • For every order, list the order number, order date, customer number, and customer name. In addition, for each order line within the order, list the part number, description, number ordered, and quoted price. Sort the records by order number.

16. Solution

17. Comprehensive Example… Your Turn • List the customer number, order number, and order total for each order with a total that exceeds $1,000. Rename the order total as ORDER_TOTAL

18. Solution… SELECT Customer_Num, Orders.Order_Num, SUM(Num_Ordered * Quoted_Price) AS ORDER_TOTAL FROM Orders, Order_Line WHERE Orders.Order_Num = Order_Line.Order_Num GROUP BY Orders.Order_Num, Customer_Num HAVING Sum (Num_Ordered * Quoted_Price) > 1000 Order By Orders.Order_Num;

19. Joining a table to itself (self-join) • Have to use an alias (in FROM clause) to treat a table as two tables. • Example: • For each pair of customers located in the same city, display the customer number, customer name, and city

20. Solution (See figures 5.12 & 5.13 on p. 147-148)

21. Set Operations • Union • Of two tables is a temp table containing every row that is in either the first table, the second table or both • Intersect (intersection) • Of two tables is a temp table containing all rows that are in both tables • Tables must be union compatible – same number of columns with identical data types and lengths • Minus (difference) • Of two tables is (a temp table) the set of all rows that are in the first table but not in the second table • Access does not support Intersect or Minus

22. Union example • List the number and name of each customer that either is represented by sales rep 65 or that currently has orders on file, or both • Solution:

23. Intersect example • List the number and name of each customer that is represented by sales rep 65 and that currently has orders on file • Solution: • Since Access does not support Intersect, use subquery

24. Minus Example • List the number and name of each customer that is represented by sales rep 65 and that does not currently have orders on file • Solution: • Since Access does not support Intersect, use subquery

25. ALL • Find the customer number, name, current balance, and rep number of each customer whose balance is greater than the individual balances of each customer of sales rep 65. • Solution:

26. ANY • Find the customer number, name, current balance, and rep number of each customer whose balance is greater than the balance of at least one customer of sales rep 65. • Solution:

27. Special Operations • Inner join • Join that compares the tables in the FROM clause and lists only those rows that satisfy the condition • All the examples we have looked at so far have used inner joins • Outer join • Used to list all rows from one of the tables in a join, regardless of whether they match any rows in the other table • Types of outer join • Left outer join • Right outer join • Full outer join

28. Outer joins • Left outer join • All rows from the table on the left will be included regardless of whether they match rows from the table on the right • Right outer join • All rows from the table on the right will be included regardless of whether they match rows from the table on the left • Full outer join • All rows from both tables will be included regardless of whether they match rows from the other table

29. Outer join example • Display the customer number, customer name, order number, and order date for all orders. Include all customers in the results. For customer that do not have orders, omit the order number and order date.

30. Outer join example solution

31. Product • Combination of all rows in the first table and all rows in the second table • Example: • Form the product of the Customer and Orders table. Display the customer number, name, order number and order date • Solution: