Select from union tsql

sql sql-server-2005 union
BlargleMonster picture BlargleMonster · Sep 13, 2012 · Viewed 55.9k times · Source

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.

Answer

Himanshu Jansari picture Himanshu Jansari · Sep 13, 2012

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'

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More