Skip to content
Advertisement

UNION two tables with conditions from the first

I have a union on the same table [MyTable] so I can select certain values as the top 5, the logic of which I’m excluding here to simplify the question (I hope):

The first table I alias as tbl1 – can I reference this alias somehow after the UNION statement so that I can exclude the results from it?

I tried like so but it doesn’t recognise tbl1

SELECT top 5 tbl1.Id, tbl1.Description, 'first'
  FROM (
           -- query [MyTable] joined with others to get particular result set
       ) as tbl1

UNION

SELECT tbl2.Id, tbl2.Description, 'second'
  FROM [MyTable] as tbl2 WHERE tbl2.Id NOT IN
     (SELECT Id FROM tbl1)

Or do I just have to redo the first query in the 2nd half in order to get those Id’s to exclude?

I’m using SQL Server 2012 for this.

Advertisement

Answer

Use a CTE:

WITH cte AS (
  SELECT top 5 tbl1.Id, tbl1.Description, 'first'
  FROM (
           -- query [MyTable] joined with others to get particular result set
  ) as tbl1
)
SELECT * FROM cte
UNION ALL
SELECT tbl2.Id, tbl2.Description, 'second'
FROM [MyTable] as tbl2 WHERE tbl2.Id NOT IN (SELECT Id FROM cte)
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement