Find Records in a Table Without Corresponding Entries in a Related Table

6,4 Tìm Records trong một Bảng Nếu không có mục tương ứng trong một Related Bảng Tôi có một tình hình mà tôi cần tìm mà khách hàng không có hoá đơn.
Nội dung trích xuất từ tài liệu:
Find Records in a Table Without Corresponding Entries in a Related Table 6.4 Find Records in a Table Without Corresponding Entries in a Related TableI have a situation in which I need to find which clients dont have invoices. How do I dothis?TechniqueTo find out which records (customers) dont have corresponding records (invoices) in arelated table, you have to have a better understanding of the types of joins that can beused between tables during queries.Before looking at the joins, following is the T-SQL statement that will be used in thisHow-To:SELECT Customers.CustomerID, Customers.CompanyNameFROM Customers LEFT OUTER JOIN OrdersON Customers.CustomerID = Orders.CustomerID WHERE Orders.CustomerID ISNULLTypes of JoinsYou can use three types of joins to bring back different information. These join typesinclude inner joins, left outer joins, and right outer joins.Inner JoinThis join displays records in which at least one record exists in each table for the joinedfield. This means that customers are displayed only if they have at least one invoice. Ifyou want to see the CompanyName and OrderDate, the SELECT statement would be asfollows:SELECT Customers.CompanyName, Orders.OrderDateFROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerIDLeft Outer JoinYou use this join when you want to see all of the records in the first table, but only thoserecords in the second table that have matching records in the first table. An example ofthis would be if some customers didnt have invoices; all the customers would appear, butonly those invoices that had customers assigned to them would be displayed.Note Normally, if your database is set up with referential integrity correctly, as Northwind is, you wont have any invoices without customers assigned to them.The example SELECT statement for this will be used in the How-To.ON Customers.CustomerID = Orders.CustomerID WHERE Orders.CustomerID ISNULLRemember, because you want only those customers without invoices, you need to checkto see where Orders.CustomerID is NULL.Note Although it is not strictly necessary to check whether the Orders.CustomersID is NULL, make sure to check for a column that would not be NULL if a record were there. OrderID would be another good column to check.Right Outer JoinAs you might have guessed by now, the right outer join is the opposite of the left outerjoin. Instead of listing all the customers and only those invoices that have customers, theright outer join shows you all records in the second table (invoices) and only thoserecords from the first table (customers) in which there is a matching record on the joinedcolumn (CustomerID).One of the ways that the right outer join is used is to find bad data, such as non-relationaldata that could result from importing data from other systems. Use a SELECT statementsimilar to the one used for the left outer join type:SELECT Customers.CustomerID, Customers.CompanyNameFROM Customers RIGHT OUTER JOIN OrdersON Customers.CustomerID = Orders.CustomerID WHERE Customers.CustomerID ISNULLYou will notice that instead of testing for Orders.CustomerID being NULL, you arechecking to see if Customers.CustomerID is NULL.StepsOpen and run the Visual Basic .NET-Chapter 6 solution. From the main form, click onthe button with the caption How-To 6.4. When the form loads, you will see the SQLString displayed with the data grid displaying customers who dont have orders (seeFigure 6.5). 1. Create a Windows Form. Then place the controls listed in Table 6.4 with the following properties set, as displayed in Figure 6.5. Table 6.4. Control Property Settings for This How-To Object Property Setting Label Text SQL Statement Label Name lblSQLString Label Text Results DataGrid Name dgResults 2. Add the following code in Listing 6.9 to the Load event of the form. (Double-click on the form to bring up the code.) The only difference in this routine from the previous How-To is the SELECT statement, which is described in the Technique section. Listing 6.9 frmHowTo6_4.vb: Using the Left Outer Join Private Sub frmHowTo6_4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load -- Build the SQL String Dim strSQL As String strSQL &= SELECT Customers.CustomerID, Customers.CompanyName strSQL &= FROM Customers LEFT OUTER JOIN Orders strSQL &= ON Customers.CustomerID = Orders.CustomerID & vbCrLf strSQL &= WHERE Orders.CustomerID IS NULL -- Store the SQL String Me.lblSQLString.Text = strSQL -- Use the SQL String to build the data adapter and fill the data table. Dim odaResults As New OleDb.OleDbDataAdapter(Me.lblSQLString.Text, _ BuildCnnStr((local), Northwind)) Dim dtResults As New DataTable() Try odaResults.Fill(dtResults) Catch excp As Exception MessageBox.Show(excp.Message) Exit Sub End Try -- Assign the data table to the data grids DataSource property Me.dgResults.DataSource = dtResults End Sub Figure 6.5. Using an outer join to retrieve records without corresponding records.CommentsThe majority of the time, you will be using the inner join rather than the outer joins.However, sometimes outer joins will be necessary, so you should experiment with themand get comfortable with them.