VB6 beginners tutorial - Learn VB6

Advanced VB6 tutorial - Learn Advanced VB6

Systems Analysis - System analysis and Design tutorial for Software Engineering

You are here: Visual Basic > Advanced VB6 tutorial > Chapter 9

Writing SQL Statements That Use Joins to Combine Data from Multiple Tables

More often than not, you will need to relate together the records from more than one table in a SQL statement

Using the Where Clause to Connect Tables

You can use the Where clause to bring data from more than one table into the result set of a query.

You can specify more than one table's data in the result set of a query by just specifying the field names from each table and the table names in the basic syntax of the Select statement. You will always want to explicitly specify the relation between the tables with either a Where clause or a Join clause, however, for the combination of the data from the two tables to be meaningful.

Assume, for example, that you would like to see a list of every order's date from an Orders table in your database along with the customer name for each order. Customer names are held in a Customers table and the Orders table contains a CustomerID field that keys to the CustomerID field in the Customers table. An example of a Select clause that would properly return the information you are seeking would read as follows:

Select [Order Date], [Customer Name] From Orders, Customers Where Orders.[Customer ID] = Customers.[Customer ID]

If it didn't include the Where clause, the Select statement would return a Cartesian product of the two tables—that is, it would match every record in Orders with every customer and return a huge, meaningless result set! Note that you don't have to specify which table a field comes from so long as that field's name is unique within the tables you have specified in the From clause. If a field's name isn't unique within the tables used in the query, specify its originating table with the tablename.fieldname syntax.

Using JOIN Clauses to Connect Tables

You can create a multitable Recordset with a query that uses a JOIN clause. There are two types of JOIN, and these two JOIN types are implemented by three different possible JOIN clause types. These types of JOIN and the clauses that implement them are as follows:

  • An equi-join or inner join. This type of join creates records in a result set only when there are matching records from both tables. You can use an INNER JOIN clause to create an equi-join.

  • An outer join. Result sets created using this type of join contain all the records from a specified master table and only those

records from a related lookup table that match the records in the master table. You can implement an outer join with either the LEFT JOIN or RIGHT JOIN clause. The difference between these two types of join is the order in which you specify the master and lookup tables.

To specify an equi-join between the Customers table and the Orders table, write a query as in the following example:

Select [Company Name], [Order Date] From Customers
INNER JOIN Orders ON Customers.[Customer ID] =
Orders.[Customer ID]

The result set would contain only matching information from the Customers and Orders tables.

If you wanted to display a list of customers and the dates of their orders, but you wanted to include even customers without any orders, you could specify this result set with a LEFT JOIN, as in the following example:

Select [Company Name], [Order Date] From Customers
LEFT JOIN Orders ON Customers.[Customer ID] =
Orders.[Customer ID]

You could achieve the same effect with a RIGHT JOIN clause, as follows:

Select [Company Name], [Order Date] From Orders
RIGHT JOIN Customers ON Orders.[Customer ID] =
Customers.[Customer ID]


<< Previous | Content | Next >>

Home | About Us | Privacy Policy | Contact Us

Copyright © | All Rights Reserved