Website Design United States, Website Design California, Website Designing United States, Website Designing California

Using A Predicated Subquery To Restrict A Join

By Mark Schweikert

If you’ve ever used SQL to retrieve information from a database then I’m sure you are familiar with the SELECT statement; it allows the user to view a specified section of the data within a database. In this tutorial I would like to demonstrate the use of a subquery as a means of expanding your control over the outcome of your SELECT statement. We will gradually work through building the statement to make it easier to understand the purpose of each of its components. The example I have chosen is a parts supply company. The owner wants to see a list of all orders where the client requested a quantity large enough to qualify for a discounted unit price. To make building the query even more difficult this company is very time driven and has a dynamic inventory; both the ClientID and the ItemNbr fields allow non-systemized entries. This gives the company great flexibility because they can start processing orders from brand new clients before they are set up in the system as well as allow a client to order an item not normally in inventory. The owner loves this feature in the software but it puts a wrinkle in our SELECT statement.

For those of us who are familiar with SQL here is the completed statement:
SELECT Orders.OrderNbr,Orders.ClientID,Clients.ClientName,Orders.ItemNbr,Orders.Qty

FROM Orders INNER JOIN Clients ON Orders.ClientID = Clients.ClientID

WHERE (Client.Active=True) AND (Orders.ItemNbr IN (SELECT ItemNbr FROM Items WHERE Orders.Qty >=DiscountQty))

For those of us who are not so familiar with SQL understanding the above statement could be a daunting task. The remainder of the tutorial is for the people who would like further explanation.

Before we get started let us define our tables that will be used in the examples.

table relationship diagram

Let us look at a SELECT statement in its simplest form.
SELECT * FROM Orders

This will return a list of everything in the Orders table.

In our example we do not wish to retrieve the Side field, (it is the line number for multi-item orders) in this query the information isn’t needed. We can eliminate that field by listing the fields we want.
SELECT OrderNbr, ClientID, ItemNbr, Qty FROM Orders

The owner isn’t interested in seeing orders from Clients who are not systemized. So we now need to restrict the results further to remove orders with ClientID's that are not in the Clients table. He also wants to see the client name. For this we will use a JOIN clause. Using the JOIN clause we will be able to filter the records and add the client name at the same time. I’ve chosen an INNER JOIN for this query because it will only pass records that meet the ON criteria. We will use a criterion that compares the ClientID from the Orders table to the ClientID from the Clients table; so any records in the Orders table that don’t have valid ClientID's will be filtered out.
SELECT Orders.OrderNbr,Orders.ClientID,Clients.ClientName,Orders.ItemNbr,Orders.Qty

FROM Orders INNER JOIN Clients ON Orders.ClientID = Clients.ClientID

We will use the WHERE clause to restrict the results to orders from active clients.

NOTE: ON and WHERE perform similar tasks. ON specifies a condition for the JOIN clause; WHERE specifies a condition for the SELECT statement.
SELECT Orders.OrderNbr,Orders.ClientID,Clients.ClientName,Orders.ItemNbr,Orders.Qty

FROM Orders INNER JOIN Clients ON Orders.ClientID = Clients.ClientID

WHERE (Client.Active=True)

Using = to set our conditions for the WHERE clause allows us to make a one-to-one comparison of a field to another field or value, but sometimes it would be beneficial to be able to compare a field to a list of static values. To do this we would replace the WHERE/= with WHERE/IN as in the example below:
SELECT Orders.OrderNbr,Orders.ClientID,Clients.ClientName,Orders.ItemNbr,Orders.Qty

FROM Orders INNER JOIN Clients ON Orders.ClientID = Clients.ClientID

WHERE (Client.Active=True) AND (Orders.ItemNbr IN (‘W010’, ‘W025’,’Z001’)

In the example we have restricted the query to orders for the three item numbers in our list. While using the WHERE/IN combination is powerful your list is hard coded within the SELECT statement. This is where our long awaited subquery comes into play; we can upgrade our static item number list with a dynamic list by using a secondary SELECT statement. In our example the owner wants to see orders for items that are already set up in inventory. We will use a SELECT statement to return a list of all items in inventory:
SELECT ItemNbr FROM Items

So our SELECT statement with subquery will look like this:
SELECT Orders.OrderNbr,Orders.ClientID,Clients.ClientName,Orders.ItemNbr,Orders.Qty

FROM Orders INNER JOIN Clients ON Orders.ClientID = Clients.ClientID

WHERE (Client.Active=True) AND (Orders.ItemNbr IN (SELECT ItemNbr FROM Items))

An advantage to using a subquery is that it allows us to refer to all of the fields from the main SELECT statement. Because of this we can add one more restriction, we need to limit the results to orders that qualify for a discount. So we can refer to the Orders.Qty field from the main statement within our subquery to compare it to the Items.DiscountQty:
SELECT Orders.OrderNbr,Orders.ClientID,Clients.ClientName,Orders.ItemNbr,Orders.Qty

FROM Orders INNER JOIN Clients ON Orders.ClientID = Clients.ClientID

WHERE (Client.Active=True) AND (Orders.ItemNbr IN (SELECT ItemNbr FROM Items WHERE Orders.Qty >=DiscountQty))

Our completed query, with the aid of a subquery, gives us the desired results. There are plenty of powerful uses for subqueries and their use should not be overlooked. I hope this tutorial has given you some ideas for using subqueries in your own SELECT statements.

 
WEB DESIGN INDIA
42 B Malviya Nagar , New Delhi-110017

Skype: manmeetsi
Email: support.webdesignindia@gmail.com
Tel: 91-011-40502005, 9810067295

 















 


© 2008-2009 dotnet4all.com