2
Objectives
You will be able to
Write SQL queries that use Join operations to
Retrieving from Multiple Tables
We often need to retrieve information
from multiple tables in a single query.
Example:
Show the name of the customer contact for all orders in the Northwinds database.
The Northwinds Database (Subset) Customers CustomerID CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone Fax Order Details OrderID ProductID UnitPrice Quantity Discount Orders OrderID CustomerID EmployeeID OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode ShipCountry Products ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued Suppliers SupplierID CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone Fax HomePage
Retrieving from Multiple Tables
The Orders table has the Customer ID but not the
customer’s contact name.
The Customers table has the contact name.
In order to determine the customer contact for an
order, we need to get the Customer ID from the
Orders table and then get the contact name for that Customer ID from the Customers table.
The SQL “join” operation makes this possible with a
single query.
Retrieve pairs of rows from two tables having identical
6
Types of Joins
SQL defines several types of joins. The simplest is the inner join.
It has the form:
SELECT TABLE1.COLUMN1, TABLE2.COLUMN2 FROM TABLE1, TABLE2
WHERE TABLE1.COLUMN_X = TABLE2.COLUMN_Y
Whatever you want from either table
Query will return selected items from every pair of rows for which this condition is true.
Inner Join Example
8
Inner Join Example
Objective:
Determine the name of the
Inner Join Example
1> SELECT ORDERS.ORDERID, CUSTOMERS.CONTACTNAME
2> FROM ORDERS, CUSTOMERS
3> WHERE CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID
4> GO ORDERID CONTACTNAME --- ---10643 Maria Anders 10692 Maria Anders 10702 Maria Anders 10835 Maria Anders 10952 Maria Anders 11011 Maria Anders ... 10860 Carine Schmitt 10971 Carine Schmitt (830 rows affected) 1>
10
Being More Selective
We could add more conditions to the
WHERE clause.
Suppose we wanted the contact name
for just one specific order.
We could add to the query:
Inner Join Example
1> SELECT ORDERS.ORDERID, CUSTOMERS.CONTACTNAME
2> FROM ORDERS, CUSTOMERS
3> WHERE CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID
4> AND ORDERS.ORDERID=11012 5> GO ORDERID CONTACTNAME --- ---11012 Peter Franken (1 rows affected) 1>
12
Table Aliases
We can give a table a temporary name
within a single SQL statement
A short name saves keystokes and makes
the statement easier to read and understand.
Write the (short) temporary name
immediately after the real table name in the FROM clause.
1> SELECT O.ORDERID, C.CONTACTNAME 2> FROM ORDERS O, CUSTOMERS C
3> WHERE C.CUSTOMERID = O.CUSTOMERID 4> AND O.ORDERID = 10261 5> GO ORDERID CONTACTNAME --- ---10261 Bernardo Batista (1 rows affected) 1> Table Aliases Table Aliases
14
Joining More than Two Tables
Any number of tables can be included in
a join operation.
Suppose we want to see the name of
each product on any order by Customer VINET
Joining More than Two Tables 1> SELECT P.ProductName
2> FROM Products P, [Order Details] OD, Orders O, Customers C
3> WHERE P.ProductID = OD.ProductID
4> AND OD.OrderID = O.OrderID
5> AND O.CustomerID = C.CustomerID
6> AND C.CustomerID = 'VINET'
7> GO
ProductName
---Queso Cabrales
Singaporean Hokkien Fried Mee Mozzarella di Giovanni
Flotemysost
Mozzarella di Giovanni Gnocchi di nonna Alice Konbu
Jack's New England Clam Chowder Inlagd Sill
Filo Mix
(10 rows affected) 1>
The Northwind Product Browser
Let’s extend the Northwind Product
Browser to show the supplier for each product.
Download the final version of the example
done in class:
http://www.cse.usf.edu/~turnerr/Software_Systems_Development/
Downloads/2011_04_05_Northwind_Product_Browser/
File Northwind_Product_Browser.zip
Expand zip file. Build and run.
18
The Northwind Product Browser
We would like to show the supplier for
each product along with the other product information.
We can use a join to get related
information along with the product information.
Products and Suppliers
The Product table includes a SupplierID.
Not meaningful to the user.
The Supplier table includes the supplier's
CompanyName
which is meaningful.
Tables are related by SupplierID
Use an inner join to get the CompanyName of
the supplier of each product from the Supplier table.
22
Class Products
Class Products is responsible for the query.
SqlCommand1.CommandText =
"SELECT P.*, S.CompanyName " +
"FROM Products P, Suppliers S " + "WHERE P.SupplierID = S.SupplierID";
Build and run.
24
Class Product
public class Product {
private String product_name; private int product_id;
private decimal unit_price; private short units_in_stock; private bool discontinued;
private String supplier_name; ...
public String Supplier_name {
get { return supplier_name; } set { supplier_name = value; } }
Class Product
// This constructor initializes a product // using a query result.
public Product(SqlDataReader rdr) { Product_name = rdr.GetString(1); Product_id = rdr.GetInt32(0); Supplier_name = rdr.GetString(10); …
Product_Information.cs
private void Display_Current_Record() { Product p = Product_List[current_record_number]; tbProductName.Text = p.Product_name; tbProductID.Text = p.Product_id.ToString(); tbUnitPrice.Text = p.Unit_price.ToString(); tbUnitsInStock.Text = p.Units_in_stock.ToString(); cbDiscontinued.Checked = p.Discontinued; tbSupplier.Text = p.Supplier_name; tbRecordNumber.Text = "Record " + (current_record_number + 1) + " of " + Product_List.Count; Update_Buttons(); }
28
List Alphabetically
Let’s show the products in alphabetical
order by ProductName.
Revised query:
sc.CommandText = "SELECT P.*, S.CompanyName " + "FROM Products P, Suppliers S " +
"WHERE P.SupplierID = S.SupplierID " + "ORDER BY P.ProductName" ;
30
