CODEWITHSUNDEEP
sqlsql-server
jamheadart
jamheadart

Reputation: 5293

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.

Upvotes: 2

Views: 1418

Answers (2)

DSR
DSR

Reputation: 722

You can also use a temp table for the same purpose based on your requirement. See the difference here.

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

SELECT * FROM #tbl1
UNION ALL
SELECT tbl2.Id, tbl2.Description, 'second'
FROM [MyTable] AS tbl2 
WHERE tbl2.Id NOT IN (SELECT Id FROM #tbl1)

Upvotes: 2

forpas
forpas

Reputation: 164069

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)

Upvotes: 1

Related Questions