How to filter results based on subquery?-CodePudding

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.

CodePudding user response：

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 --??

CodePudding user response：

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.