Return to Using Subqueries to Select Records Using Subqueries to Select Records Written by Bill Graziano on 17 August 2000 John writes "Okay, here's my problem. I have these two tables: INVOICE, which includes everything about a particular invoice including a vendor_id. The second table is VENDOR, which includes the vendor name for each vendor_id. What I want to be able to do is select from the VENDOR table a list of all of the vendor_names that have appeared in the INVOICE table (even if they have appeared in the INVOICE table many times, I just want them to show up once). Is this possible?" I'll show the SQL for this and then explain what it does: `SELECT VendorName FROM Vendor WHERE VendorID IN (SELECT DISTINCT VendorID FROM Invoice)` The IN clause allows you to compare a field to a list of values. For example, if you wanted a certain list of VendorID's you might write something like this: `SELECT VendorName FROM Vendors WHERE VendorID IN (123, 556, 234)` This statement is the equivalent of `SELECT VendorName FROM Vendors WHERE VendorID = 123 OR VendorID = 556 OR VendorID = 234` You could easily use this in your client to dynamically build SQL statements. Your IN clause can also include a SELECT statement which is what we did in our first example. Basically we're having SQL Server dynamcially generate our list of VendorID's based on the SELECT statement in the subquery. The DISTINCT clause isn't technically needed in this case. It makes the code more readable to me. I can easily see the intent of the statement. You could easily put a where clause on the SELECT in the subquery to restrict the rows even further. Now if you have your heart set on a join, you can create a statement that looks like this: SELECT DISTINCT VendorName FROM Vendor INNER JOIN Invoice ON Vendor.VendorID = Invoice.VendorID The DISTINCT clause will cause each vendor to be displayed only once. By joining to the Invoice table we've limited it to Vendors that appear in that table.

Return to Using Subqueries to Select Records

Using Subqueries to Select Records

Written by Bill Graziano on 17 August 2000

John writes "Okay, here's my problem. I have these two tables: INVOICE, which includes everything about a particular invoice including a vendor_id. The second table is VENDOR, which includes the vendor name for each vendor_id. What I want to be able to do is select from the VENDOR table a list of all of the vendor_names that have appeared in the INVOICE table (even if they have appeared in the INVOICE table many times, I just want them to show up once). Is this possible?"

I'll show the SQL for this and then explain what it does:

SELECT VendorName FROM Vendor WHERE VendorID IN (SELECT DISTINCT VendorID FROM Invoice)

The IN clause allows you to compare a field to a list of values. For example, if you wanted a certain list of VendorID's you might write something like this:

SELECT VendorName FROM Vendors WHERE VendorID IN (123, 556, 234)

This statement is the equivalent of

SELECT VendorName FROM Vendors WHERE VendorID = 123 OR VendorID = 556 OR VendorID = 234

You could easily use this in your client to dynamically build SQL statements. Your IN clause can also include a SELECT statement which is what we did in our first example. Basically we're having SQL Server dynamcially generate our list of VendorID's based on the SELECT statement in the subquery. The DISTINCT clause isn't technically needed in this case. It makes the code more readable to me. I can easily see the intent of the statement.

You could easily put a where clause on the SELECT in the subquery to restrict the rows even further. Now if you have your heart set on a join, you can create a statement that looks like this:

SELECT DISTINCT VendorName
FROM Vendor
INNER JOIN Invoice
ON Vendor.VendorID = Invoice.VendorID

The DISTINCT clause will cause each vendor to be displayed only once. By joining to the Invoice table we've limited it to Vendors that appear in that table.