Union Query 1

[munchen]

I have the following Union in SQL Server 2005:

SELECT c_num, bA_id AS b_id
FROM tblA

UNION

SELECT c_num, bB_id AS b_id
FROM tblB

WHERE (b_id = '999')

I keep getting the following error:

Invalid column name 'b_id'.

Any ideas what I have done wrong?

 

[gmmastros]

In a union query, the where clause does NOT apply to the whole thing, but to individual parts. So, to fix this, you could make a derived table and apply the where clause to it, or you could duplicate the where clause for each part. From a performance perspective, it's better to have multiple where clauses.

Select c_num, b_id
From   (
       SELECT c_num, bA_id AS b_id
       FROM tblA

       UNION

       SELECT c_num, bB_id AS b_id
       FROM tblB
       ) As A
WHERE (A.b_id = '999')

SELECT c_num, bA_id AS b_id
FROM tblA
WHERE ([!]bA_id[/!] = '999')

UNION

SELECT c_num, bB_id AS b_id
FROM tblB
WHERE ([!]bB_id[/!] = '999')

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[munchen]

gmmastros

Yes that makes perfect sense now.

Thanks a lot.