CODEWITHSUNDEEP
sql-serverleft-join
Richard F.
Richard F.

Reputation: 67

SQL Server: Select from table based on subquery from 2 other tables

Apologies in advance if this has been asked before, but most of the similar examples I've seen only involve 2 tables and I'm having difficulty adapting my situation.

I have a query to select the Product Number and Unit Of Measure from table ProductList that are not in another table, PriceFile:

SELECT DISTINCT Prod_Num, [PriceFile].UM FROM [dbo].[PriceFile]
LEFT JOIN [dbo].[ProductList]
ON [PriceFile].Prod_Num = [ProductList].Product
WHERE [ProductList].Product IS NULL

With the data currently in the tables, this provides me with this:

Prod_Num    UM
13717       HC

I now need to use the results of this query to query a third table, Products, which I attempt to do like this:

SELECT DISTINCT ProductID AS proCode,Prod_Desc AS proName,
UPC_Code AS proBarCode,UM_SOLDBY AS untCode
FROM [dbo].[Products]
WHERE ProductID IN 
(
SELECT DISTINCT Prod_Num, [PriceFile].UM FROM [dbo].[PriceFile]
LEFT JOIN [dbo].[ProductList]
ON [PriceFile].Prod_Num = [ProductList].Product
WHERE [ProductList].Product IS NULL
)

But of course this gives me the error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

And I'm not sure where to go from here. If I alter the subquery so that it only has Prod_Num, I get the Product Number I'm looking for, but I get both of the Units Of Measure for it that exist in the Products table.

proCode untCode
13717   DZ
13717   HC

And I only want the line with HC as the UM.

Like I said, I've tried multiple ways of JOIN-ing or WHERE EXIST-ing, but the 3rd table aspect is throwing me off.

Any help would be much appreciated.

Upvotes: 1

Views: 65

Answers (3)

Richard F.
Richard F.

Reputation: 67

Using the logic provided by @LONG, I've figured it out. I altered my initial query that gets used as a subquery with logic pulled from this question and then tweaked LONG's suggestion a bit. The full solution looks like this:

SELECT DISTINCT A.[ProductID] AS proCode,
   A.[Prod_Desc] AS proName,
   A.[UPC_Code] AS proBarCode,
   A.[UM_SOLDBY] AS untCode
FROM [dbo].[Products] AS A
INNER JOIN 
(
SELECT DISTINCT Prod_Num, PF.UM FROM [xSales].[dbo].[TCH_PriceFile] PF
WHERE NOT EXISTS (
SELECT Product, UM FROM [TCH_ProductList] PL
WHERE
PF.Prod_Num = PL.Product AND PF.UM = PL.UM)
) AS B
ON B.Prod_Num = A.[ProductID] AND B.UM = A.[UM_SOLDBY]

Thanks to @LONG and @Kevin for their suggestions. Very much appreciated.

Upvotes: 1

Kevin
Kevin

Reputation: 241

I'm not sure exactly what you are looking for but you have the untCode column in your final select output it is going to return all distinct results of that column, which is why you get the 2 rows.

I built what I think you have in SQL Fiddle. Could you update this maybe and provide detail of your desired output.

http://sqlfiddle.com/#!6/75687/1

Upvotes: 1

LONG
LONG

Reputation: 4620

You could eliminate those dups by joining two results then filter desired row.

SELECT DISTINCT A.ProductID AS proCode,
       A.Prod_Desc AS proName,
       A.UPC_Code AS proBarCode,
       A.UM_SOLDBY AS untCode
FROM [dbo].[Products] as A
INNER JOIN 
(
SELECT DISTINCT Prod_Num, [PriceFile].UM FROM [dbo].[PriceFile]
LEFT JOIN [dbo].[ProductList]
ON [PriceFile].Prod_Num = [ProductList].Product
) as B
ON B.Prod_Num = A.ProductID
WHERE B.Product IS NULL

Upvotes: 1

Related Questions