Select from union tsql

Is it possible to select from the result of a union? For example I'm trying to do something like:

SELECT A
FROM
(
    SELECT A, B FROM TableA
    UNION
    SELECT A, B FROM TableB
)
WHERE B > 'some value'

Am I missing anything or making an assumption about how this works? I'm using MSSQL 2005 so any solution will need to conform to what I can do there.

Answers


You should give alias to your table. So try this:

SELECT A
FROM
(
    SELECT A, B FROM TableA
    UNION
    SELECT A, B FROM TableB
) AS tbl
WHERE B > 'some value'

Give Alias name without which you will receive

Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'WHERE'.

Try this (Give Alias Name)

DECLARE @TableA Table(A int, B int)
DECLARE @TableB Table(A int, B int)

INSERT INTO @TableA VALUES(1,1)
INSERT INTO @TableA VALUES(1,2)
INSERT INTO @TableA VALUES(1,3)
INSERT INTO @TableA VALUES(1,4)

INSERT INTO @TableB VALUES(2,1)
INSERT INTO @TableB VALUES(2,2)
INSERT INTO @TableB VALUES(2,3)
INSERT INTO @TableB VALUES(2,4)

SELECT A
FROM
(
    SELECT A, B FROM @TableA
    UNION
    SELECT A, B FROM @TableB
)X -- here X is table Alias
WHERE B > 0

Result

A

1
1
1
1
2
2
2
2

Need Your Help

What is the Maximum Size of the Volley-Library Request-Queue

android android-volley

I am using Volley library in Android, I want to know what is maximum size of queue is allowed using Volley library. There is nothing I found related to this. As I know you need to add the network r...