3

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?

  • Are sure it works fine? There is an extra comma. – paparazzo Feb 1 '16 at 23:30
3

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
)
  • 1
    Extra comma in FROM Note – paparazzo Feb 1 '16 at 23:36
  • Cheers dude, I just copied & pasted the OP's code - fixed ;-) – Mr.Brownstone Feb 1 '16 at 23:43
  • Excellent! You second example with the entire result set is spot on. Thanks. – Bjørn Fridal Feb 2 '16 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.