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

Question

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.

score 1 · Answer 1 · answered Aug 29 '17 at 19:54

1

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

answered Aug 29 '17 at 19:54

LONG

4,490
2
17
35

This seems like it should work, but it's returning no values. The columns are empty. I'll try playing with it some and see if I can tweak it. Thanks. – Richard F. Aug 29 '17 at 20:44

score 1 · Answer 2 · answered Aug 29 '17 at 20:09

1

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

answered Aug 29 '17 at 20:09

Kevin

241
6
21

I'm essentially trying to pull additional info from the Products table for any product and UM that's in PriceFile, but not in ProductList. The Fiddle seems to do exactly what my already existing query is doing. The top query returns only the single Prod+UM combo in PriceFile, but not in ProductList. The 2nd query returns both Prod+UM combos for the Prod from the first query, but it's not filtering out the UM that isn't listed in the first. Does that make more sense? – Richard F. Aug 29 '17 at 20:39

score 1 · Accepted Answer · answered Aug 29 '17 at 22:09

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.

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

3 Answers3