Using Subqueries to Select Records

By Bill Graziano on 17 August 2000 | 3 Comments | Tags: SELECT

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.

Discuss this article: 3 Comments so far. Print this Article. This page has been read 58,425 times.

If you like this article you can sign up for our newsletter. We send it out each week that we post a new article. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Email This • Subscribe to this feed • Kick it • Save to del.icio.us • View blog reactions

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

Server Side Paging using SQL Server 2005 (4 January 2007)

Using XQuery, New Large DataTypes, and More (9 May 2006)

Counting Parents and Children with Count Distinct (10 January 2006)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email:

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

- Sponsor's Message -

SQLShare.com Videos

Using DatePart and DateName

Ever want to get the month out of a date as a number, or as literal text? Many people will do it by parsing the date as a string, but we've got some built in functions that will do it cleanly and consistently.

Renaming a Database

You won't do it often, but it's nice to know how, and you're not still using sp_renamedb are you? Join us for a quick look at how to use the alter syntax to change the db name along with a tip on how to quickly disconnect any remaining users from the database.

File Share Subscriptions in Reporting Services

Whether you want to generate PDF invoices for customers or do a daily export that will get processed by one of your vendors, the ability to deliver reports to a file share is a useful and simple feature baked in to Reporting Services. In this lesson Devin will show you how to do it and how to set most of the common options.

File Share Subscriptions in Reporting Services

Using DB_ID and DB_Name Functions

Simple but effective, DB_ID and DB_Name give you a concise way to look up the id of a database from a name, or look up the name of a database from an id. There are times when you'll need to write the join to sys.sysdatabases, but when all you need is a quick conversion, these functions get it done.