UNION SQL statement not working


UNION SQL statement not working



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.




SQL Four Tables One Recordset

1:



What's wrong with this SQL statement?
You have a scoping issue.


Subsonic 2: object brings back multiple database tables info
A union combines two separate queries.


Get primary key of aggregated, group by SQL query using PostgreSQL
So if you separate your queries out:.
SQL select: two-dimensional select with variable column count
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.


Structure within staging area of data warehouse
There are a few other ways to accomplish what you're trying to do:.
Dependency in identity column values after multiple SQL inserts
  • 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.


How do I write this SQL query?
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 


2:


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?.


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..


4:


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..


5:


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 



96 out of 100 based on 86 user ratings 1086 reviews

@