How to filter results based on subquery?

Question

I have the following query:

SELECT 
    [Id], [Number] AS [Purchase Order], [OldProduct] AS [Old Product], 
    (SELECT [Id]
     FROM  
         (SELECT [Id], [Name]
          FROM [Products]
          UNION ALL
          SELECT [ProductId] AS [Id], [Alias] AS [Name]
          FROM [ProductAliases]) AS [ProductNames]
     WHERE [ProductNames].[Name] = po.[OldProduct]) AS [ProductId]
FROM 
    [PurchaseOrders] po

How could I limit the results to only those rows where [ProductId] is NULL?

If I just add a WHERE clause at the end, that doesn't work. There is no error but the PurchaseOrders table has a ProductId column so it just references that. Also, I believe the SELECT clause runs at the end so I don't think that makes sense to filter it before the SELECT clause.

I would probably use a cross apply here, sample data would really help. — Stu, Oct 06 '22 at 18:31
Where which `([ProductId])` is `null`? The one in `ProductAliases` or the result of the subquery? DDL would also help. — HABO, Oct 06 '22 at 18:47
@HABO: The result of the subquery. If eithe [Products.Id] or [ProductAliases.ProductId] is not null, then it shouldn't be included in the results. — Jonathan Wood, Oct 06 '22 at 20:53

Ross Bush · Answer 1 · 2022-10-06T19:13:14.413

The question is vague, it appears that you are attempting to identify where the inner query has null records, but you need the data from the outer query in full, even when the inner query has nulls?

You can try the query below and see if it gets you close. I moved the query from the select clause as left joins. If this is not what you are asking just comment and I will delete the answer.

SELECT 
    [Id], 
    [Number] AS [Purchase Order], 
    [OldProduct] AS [Old Product], 
    ISNULL(PA.ProductId, P.ID) AS ProductId
FROM 
    [PurchaseOrders] po 
    LEFT JOIN Products P ON P.[Name] = po.OldProduct
    LEFT JOIN ProductAliases PA ON PA.Alias = po.OldProduct     
WHERE
    P.ID IS NULL --??

Sorry, I thought I was clear. I want the exact same results as my original query, except any results where `Id` is not null be eliminated. I'm trying to figure out how joins produce the same result as a union, but you may be right. Checking... — Jonathan Wood, Oct 06 '22 at 20:05

score 0 · Accepted Answer · answered Oct 07 '22 at 02:02

You cannot directly access the value of an expression in a select list within the where clause for the select. (Reference alias (calculated in SELECT) in WHERE clause.)

By wrapping the existing query in a CTE you can use the subquery result in a later select to filter the rows:

with BallOfWax as (
  SELECT 
    [Id], [Number] AS [Purchase Order], [OldProduct] AS [Old Product], 
    (SELECT [Id]
     FROM  
         (SELECT [Id], [Name]
          FROM [Products]
          UNION ALL
          SELECT [ProductId] AS [Id], [Alias] AS [Name]
          FROM [ProductAliases]) AS [ProductNames]
     WHERE [ProductNames].[Name] = po.[OldProduct]) AS [ProductId]
FROM 
    [PurchaseOrders] po )
select Id, [Purchase Order], [Old Product], ProductId
  from BallOfWax
  where ProductId is NULL;

Since there is only one ProductId in the BallOfWax columns there is also no confusion as to which column will be used.

How to filter results based on subquery?

2 Answers2