Đang chuẩn bị liên kết để tải về tài liệu:
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. | 6.4 Find Records in a Table Without Corresponding Entries in a Related Table I have a situation in which I need to find which clients don t have invoices. How do I do this Technique To find out which records customers don t have corresponding records invoices in a related table you have to have a better understanding of the types ofjoins that can be used between tables during queries. Before looking at the joins following is the T-SQL statement that will be used in this How-To SELECT Customers.CustomerlD Customers.CompanyName FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID Orders.CustomerID WHERE Orders.CustomerID IS NULL Types of Joins You can use three types of joins to bring back different information. These join types include inner joins left outer joins and right outer joins. Inner Join This join displays records in which at least one record exists in each table for the joined field. This means that customers are displayed only if they have at least one invoice. If you want to see the CompanyName and OrderDate the SELECT statement would be as follows SELECT Customers.CompanyName Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID Orders.CustomerID Left Outer Join You use this join when you want to see all of the records in the first table but only those records in the second table that have matching records in the first table. An example of this would be if some customers didn t have invoices all the customers would appear but only 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 won t have any invoices without customers assigned to them. The example SELECT statement for this will be used in the How-To. ON Customers.CustomerlD Orders.CustomerlD WHERE Orders.CustomerlD IS NULL Remember because you want only those customers without invoices you need to check to see where Orders.CustomerlD is NULL. Note .