Tài liệu Retrieve Unique Records Using Only a Select Query docx

6.1 Retrieve Unique Records Using Only a Select Query

I need to figure out which customers have invoices. The problem is that when I join the

Customers with the Orders tables, I get the customers listed for each order. I only want

each customer listed once. How do I return only those customers who have orders, but

only once?

Technique

For this How-To, you will be using the DISTINCT clause on a SQL SELECT statement

to limit the data to unique values. When you include the DISTINCT clause, SQL Server

uses the columns that are returned to determine how to limit the data.

For the opposite affect, you can include the ALL clause, although it is not necessary

because this is the default. You will create two SELECT statements for this task. The first

one is for all records:

SELECT Customers.CompanyName FROM Customers INNER JOIN Orders ON

Customers .CustomerID =

Orders.CustomerID

To limit the records, use the DISTINCT clause:

SELECT DISTINCT Customers.CompanyName

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Steps

Open and run the Visual Basic .NET-Chapter 6 solution. From the main form, click on

the button with the caption How-To 6.1. When the form loads, you will see two option

buttons, Show All and Distinct, with Show All selected. The SELECT statement showing

an inner join between customers and order is displayed in a Label control. You will also

see a DataGrid control filled with multiple entries of customers displayed (see Figure

6.1).

Figure 6.1. A common problem with inner joins is retrieving multiple records when

you want to see just one per occurrence.