UNION SQL statement not working

Question

Hi,

Why is this wrong and how can I put it right?

SELECT     PublicationID
FROM         (SELECT DISTINCT pat.PublicationID
                       FROM          dbo.PubAdvTransData AS pat INNER JOIN
                                          dbo.PubAdvertiser AS pa ON pat.AdvTransID =   pa.AdvTransID
                       WHERE      (pat.LastAdDate > '7 / 1 / 2009') AND (pat.PublicationID = 29171)) AS table1
UNION
SELECT     PublicationAreaBuy.AreaBuyID AS PublicationID
FROM         PublicationAreaBuy INNER JOIN
                      table1 AS table1_1 ON table1.publicationID =     PublicationAreaBuy.PublicationID

The error is that table1 is an invalid object.

Thanks.

Answer 1

Well.. It is.

The two select statements are distinct from each other even though the results are joined together by the union.

What SQL server are you using including version?

Answer 2

You have a scoping issue. A union combines two separate queries. So if you separate your queries out:

SELECT PublicationID
FROM ( SELECT DISTINCT pat.PublicationID
       FROM dbo.PubAdvTransData AS pat 
       INNER JOIN dbo.PubAdvertiser AS pa 
         ON pat.AdvTransID =   pa.AdvTransID
       WHERE (pat.LastAdDate > '7 / 1 / 2009') AND (pat.PublicationID = 29171)
     ) AS table1



SELECT PublicationAreaBuy.AreaBuyID AS PublicationID
FROM PublicationAreaBuy 
INNER JOIN table1 AS table1_1 
  ON table1.publicationID = PublicationAreaBuy.PublicationID

You can see that in the second query there's no such thing as table1. There are a few other ways to accomplish what you're trying to do:

• duplicate the subquery (ugly)
• use a Common Table Expression (only if this is T-SQL)
• use a #temporary @table (again, I only know how to do this in T-SQL)

If we were to duplicate the subquery, it'd look like this. However, the top query has no conditions, so it doesn't need to be a subquery:

SELECT DISTINCT pat.PublicationID
FROM dbo.PubAdvTransData AS pat 
INNER JOIN dbo.PubAdvertiser AS pa 
   ON pat.AdvTransID =   pa.AdvTransID
WHERE (pat.LastAdDate > '7 / 1 / 2009') AND (pat.PublicationID = 29171)


UNION

SELECT PublicationAreaBuy.AreaBuyID AS PublicationID
FROM PublicationAreaBuy 
INNER JOIN ( SELECT DISTINCT pat.PublicationID
       FROM dbo.PubAdvTransData AS pat 
       INNER JOIN dbo.PubAdvertiser AS pa 
         ON pat.AdvTransID =   pa.AdvTransID
       WHERE (pat.LastAdDate > '7 / 1 / 2009') AND (pat.PublicationID = 29171)
     ) AS table1 
  ON table1.publicationID = PublicationAreaBuy.PublicationID

Answer 3

AFAIK, you can't refer to one query from the other. The two queries you are unioning together must be separate and distinct.

When you think about this, it makes sense, since all a union really does is serially splice the output from the two queries together.

Answer 4

Try using a CTE instead of your first query

WITH MyCTE AS 
(
    SELECT DISTINCT pat.PublicationID
    FROM   dbo.PubAdvTransData AS pat 
        INNER JOIN dbo.PubAdvertiser AS pa ON pat.AdvTransID = pa.AdvTransID
    WHERE (pat.LastAdDate > '7 / 1 / 2009') AND (pat.PublicationID = 29171)
)
SELECT PublicationAreaBuy.AreaBuyID AS PublicationID
FROM   PublicationAreaBuy 
    INNER JOIN MyCTE ON MyCTE.publicationID = PublicationAreaBuy.PublicationID

Answer 5

You could use a temp table, but i feel like there could be a better way altogether. Without knowing your db schema, or what you are trying to achieve, its hard to recommend anything else.

CREATE TABLE #temp
(
    PublicationID int
)

SELECT DISTINCT INTO #temp pat.PublicationID
                  FROM dbo.PubAdvTransData AS pat 
                  INNER JOIN dbo.PubAdvertiser AS pa ON pat.AdvTransID = pa.AdvTransID
                  WHERE (pat.LastAdDate > '7 / 1 / 2009') AND (pat.PublicationID = 29171)

    SELECT PublicationID
    FROM   #temp
    UNION
    SELECT PublicationAreaBuy.AreaBuyID AS PublicationID
    FROM   PublicationAreaBuy 
    INNER JOIN #temp As Table1 ON Table1.PublicationID = PublicationAreaBuy.PublicationID

DISCLAIMER: I hand typed this quickly so check for syntax errors before running.