5

I have a query that looks like this:

SELECT PubKey, Title FROM Publication
UNION
SELECT NoteKey, Title, FROM Note

Which works fine. My trouble start when I try to add an EXIST to it:

SELECT PubKey, Title FROM Publication
UNION
SELECT NoteKey, Title, FROM Note
WHERE EXISTS (SELECT * FROM UserPublication WHERE UserPublication.PubKey = Publication.PubKey)

That throws this error: The multi-part identifier "PubKey" could not be bound.

Could it be written another way?

Bjørn Fridal
  • 65
  • 1
  • 1
  • 4

2 Answers2

5

The WHERE clause is specific to each individual SELECT so you would need to move the one you have to the first SELECT like this:

SELECT PubKey, Title FROM Publication
WHERE EXISTS (
    SELECT * 
      FROM UserPublication 
     WHERE UserPublication.PubKey = Publication.PubKey
)
UNION
SELECT NoteKey, Title FROM Note

However, if you want to specify the WHERE clause across the entire result set. This must be done like so:

SELECT * FROM (
    SELECT PubKey, Title FROM Publication
    UNION
    SELECT NoteKey, Title FROM Note
) AS A
WHERE EXISTS (
    SELECT * 
      FROM UserPublication 
     WHERE UserPublication.PubKey = A.PubKey
)
Mr.Brownstone
  • 13,242
  • 4
  • 38
  • 55
0
/** Reference to the following script **/
DECLARE @chk NVARCHAR(1)='B';
/** change @chk as 'C' to see the result**/
WITH T1 AS(SELECT * FROM(VALUES('A',1))a(F1,F2)
),T2 AS(SELECT * FROM(VALUES('B',2))a(F1,F2)
),T3 AS(SELECT * FROM(VALUES('C',3))a(F1,F2)
),CHK AS(SELECT CASE WHEN EXISTS(SELECT * FROM T2 WHERE F1=@chk) THEN 'B' ELSE 'C' END[chk_f]
) SELECT * FROM T1 UNION SELECT T2.* FROM T2,CHK WHERE chk_f='B' UNION SELECT T3.* FROM T3,CHK WHERE chk_f='C'
Michael Yeh
  • 1