In order to bulk import xml data from a file, you have to apply SINGLE_BLOB option. It makes sure that...
I am using Microsoft AdventureWorks database. In this article, I am going to explain how to return only one record from the left outer join.
Regular Left Outer Join
As you would notice, it is one-to-many relationship between Product & ProductInventory . For example, Product ID 1 has 3 records in ProductInventory table
SELECT P.ProductID, ProductNumber, PIV.Quantity FROM Production.Product P LEFT OUTER JOIN Production.ProductInventory PIV ON PIV.ProductID = P.ProductID ORDER BY P.ProductID, piv.locationid
Now as you can see, I am using outer apply to join both tables. however I am joining Product record only with top 1 record of ProductInventory. Even though Product ID 1 has 3 records in ProductInventory, only the top 1 is returned in resultset.
SELECT P.ProductID, ProductNumber, A.Quantity from Production.Product P OUTER APPLY (SELECT TOP 1 * FROM Production.ProductInventory PIV WHERE PIV.ProductID = P.ProductID ORDER BY PIV.Quantity DESC) AS A ORDER BY P.ProductID