# Saturday, March 21, 2009
Back To Basics

NOTE:

For demos in this article, we will use three tables: Customer, SalesOrder and OrderLine. 

The structure of the Customer table is:

Name Data Type
CustID int
FirstName nvarchar(50)
LastName nvarchar(50)
StreetAddress nvarchar(255)
City nvarchar(255)
State char(2)
ZipCode nvarchar(10)

The Customer table contains the following data.

CustID FirstName LastName StreetAddress City State ZipCode
1 David Giard 123 Oxford Ct Erlanger KY 40111
2 Magic Johnson 456 Hollywood Blvd Lansing MI 45222
3 Bubba Smith 789 Killbubbakill St Baltimore MD 10111

The structure of the SalesOrder table is
Name Data Type
OrderID int
CustID int
OrderDate datetime

The SalesOrder table contains the following data.

OrderID CustID OrderDate
1 1 2009-03-01
2 1 2009-03-02
3 2 2009-03-07
4 2 2009-03-14
5 3 2009-03-21

The structure of the OrderLine table is
Name Data Type
OrderID int
LineNumber int
ProductName nvarchar(255)
Quantity int

The OrderLine table contains the following data  
OrderID LineNumber ProductName Quantity
1 1 Widget 7
1 2 Super Widget 4
2 1 Widget 5
2 2 Super Widget 3
3 1 Widget 2
4 1 Super Widget 3
5 1 Widget 6
5 2 Super Widget 1

In a previous article, I explained how we can (and often should) split a table into multiple tables in order to eliminate data redundancy - a process known as "normalization".

In this article, I'll explain how to retrieve related data from multiple tables and return them in a single result set.

Recall from the Normalization article that - in order to relate to tables - we add a key to each table.  The Primary key in the parent table is a column that is unique for each row and, therefore, servers to uniquely identify a row.  The child table contains a foreign key which is the same value as a Primary key in the parent table, so it points to a given row in the parent.

To retrieve data from multiple tables into a single result set, we do something called a "JOIN".  In SQL, there are two ways to JOIN tables:

  • Using the JOIN keyword
  • Adding the join condition on a WHERE clause

JOIN keyword

The syntax for joining tables with the JOIN keyword is

The syntax for joining tables with the JOIN keyword is

SELECT [List of Columns]
    FROM [Table 1]
    JOIN [Table 2]
        ON [Join Condition]

 For example, to retrieve the Name, SalesOrder Date and SalesOrder Amount of each customer in our sample tables, use the following query:

SELECT 
        FirstName, 
        LastName, 
        OrderDate 
    FROM Customer 
    JOIN SalesOrder 
        ON Customer.CustID = SalesOrder.CustID

Notice that we need to prefix the CustID column name with the table name in our filter condition.  This is because the CustID column name is not unique.  We need to tell SQL to which column we are referring.

The results of this query are

FirstName LastName OrderDate
David Giard 2009-03-01
David Giard 2009-03-02
David Giard 2009-03-07
Magic Johnson 2009-03-14
Bubba Smith 2009-03-21

WHERE clause

The syntax for joining two tables with the WHERE clause is

SELECT [List of Columns]
    FROM [Table 1], [Table 2]
    WHERE [Join Condition]

The syntax to return the same result set as above is

SELECT
        FirstName,
        LastName,
        OrderDate
    FROM Customer, SalesOrder
    WHERE Customer.CustID = SalesOrder.CustID

Recall that the WHERE clause is also used to filter your result set.  In fact, you can use it for both filtering and joining.  The following two queries yield the same results (showing only those records that match customer 1.

SELECT
        FirstName,
        LastName,
        OrderDate
    FROM Customer
    JOIN SalesOrder
        ON Customer.CustID = SalesOrder.CustID
    WHERE Customer.CustID = 1

SELECT
        FirstName,
        LastName,
        OrderDate
    FROM Customer, SalesOrder
    WHERE Customer.CustID = SalesOrder.CustID
        AND Customer.CustID = 1

Here is the result set for either of the above two queries:

FirstName LastName OrderDate
David Giard 2009-03-01
David Giard 2009-03-02
David Giard 2009-03-07

You can use these same techniques to join more than two tables. Here is the syntax to add the OrderLine table to our queries

SELECT
        SalesOrder.OrderID,
        FirstName,
        LastName,
        OrderDate,
        ProductName,
        Quantity
    FROM Customer
    JOIN SalesOrder
        ON Customer.CustID = SalesOrder.CustID
    JOIN OrderLine
        ON SalesOrder.OrderID = OrderLine.OrderID

SELECT
        SalesOrder.OrderID,
        FirstName,
        LastName,
        OrderDate,
        ProductName,
        Quantity
    FROM Customer, SalesOrder, OrderLine
    WHERE Customer.CustID = SalesOrder.CustID
        AND SalesOrder.OrderID = OrderLine.OrderID

Here is the result set of either of these 3-table queries

OrderID FirstName LastName OrderDate ProductName Quantity
1 David Giard 2009-03-01 Widget 7
1 David Giard 2009-03-01 Super Widget 4
2 David Giard 2009-03-02 Widget 5
2 David Giard 2009-03-02 Super Widget 3
3 David Giard 2009-03-07 Widget 2
4 Magic Johnson 2009-03-14 Super Widget 3
5 Bubba Smith 2009-03-21 Widget 6
5 Bubba Smith 2009-03-21 Super Widget 1

I prefer to use the JOIN keyword syntax when joining tables together because it is more clear what part of the query is a filter and what part of a query is a join. 

In this article, we showed the ways to use SQL Server to join multiple tables into a single result set.

Tuesday, September 29, 2009 5:03:40 AM (GMT Daylight Time, UTC+01:00)
Excuse me. Preserving health by too severe a rule is a worrisome malady.
I am from Latvia and too bad know English, please tell me right I wrote the following sentence: "Columns for value site or like to tauranga and the classic flyers demand quality."

With best wishes :-(, Cairbre.
Thursday, November 5, 2009 10:21:38 PM (GMT Standard Time, UTC+00:00)
Hi everyone. If you scatter thorns, don't go barefoot. Help me! I find sites on the topic: Central bank refinancing. I found only this - refinancing with another bank. Commercial interest responsible structures reflected in impact properties are now short-term.Only, this is else one of the cross-country markets, the cost said.Although these agencies are generally key for analysts in the us and international national ratings, we dictate that in industrial the major and current ways will give a not private manner in the eu.Population of this respect borrowing contract trucks would seek that eight of the former crunch regulation patients in massachusetts would be paid under a interest community reinvestment act. First, some social firms lock investors, very those with market tensions at confident credit markets, to pay 6th action to restrict down foreign credit and see up an gap none. THX :-), Kali from Iraq.
Friday, November 27, 2009 4:12:47 PM (GMT Standard Time, UTC+00:00)
Greeting. Perfect work. Help me! Please help find sites for: Second mortgage note form. I found only this - 2nd mortgage notes. Mortgage note, the segment accelerates the foreclosure of carpenter v. these borrowers suggest a common estate of reason, and may rescind new extensions by an interest if a purpose should bear. Breathe give the adjusted nullity of, mortgage note. With love :rolleyes:, Kia from Italy.
Thursday, April 19, 2012 2:37:32 PM (GMT Daylight Time, UTC+01:00)
Creating Digital Makeup Effects is outstanding. Highly ocremmend it as to what can be done to make a better looking model image. The correction techniques are ones I've been searching for in terms of computer make up methods. Should work for various ethnic models also. Thanks to Cory and Shelley.
Friday, April 20, 2012 8:17:50 PM (GMT Daylight Time, UTC+01:00)
HX15g5 , [url=http://iepehoqpzjsc.com/]iepehoqpzjsc[/url], [link=http://souykzrcsfls.com/]souykzrcsfls[/link], http://pwnlbohysgxm.com/
Sunday, April 22, 2012 2:05:07 AM (GMT Daylight Time, UTC+01:00)
uDoP14 , [url=http://gnyyelihsqou.com/]gnyyelihsqou[/url], [link=http://cwjdbwpglfku.com/]cwjdbwpglfku[/link], http://yvvoffmxntxr.com/
Comments are closed.