SQL Union problem

Question

Here is my query:

SELECT publicationId AS PublicationID
  FROM dbo.PublicationOwner
 WHERE ownerId = 31331
UNION
SELECT AreaBuy.AreaBuyID AS PublicationID
  FROM AreaBuy 
  JOIN PublicationAreaBuy ON AreaBuy.AreaBuyID = PublicationAreaBuy.AreaBuyID
 WHERE PublicationAreaBuy.PublicationID IN (SELECT publicationId
                                              FROM dbo.PublicationOwner
                                             WHERE ownerId = 31331)
ORDER BY publicationId

What I am trying to do is:

Get the list of publication ID's where the owner id is equal to 31331 and add in (union) any areabuy ids (as publication id) if the publication id in the area buy table are in the results from the first query.

What is wrong? And yes, 31331 is the correct owner id and does return a publication ID which is also in the area buy table.

Answer 1

Looks like a classic mix up. I dont see any similarity between attributes publicationId and AreaBuyId. It could be they are just very badly named columns :-). A union of such different attributes doesnt seem like the right way to go.

Why doesnt :

SELECT O.publicationId , A,AreaBuyId
  FROM dbo.PublicationOwner O
  LEFT OUTER JOIN dbo.AreaBuy A
   ON O.AreaBuyId = A.AreaBuyId
 WHERE OwnerId =31331

Get you what you want?

Answer 2

I like the idea of a recursive CTE for this, since we want to add to the original list.

DECLARE @ownerid INT;
SET @ownerid = 31331;

WITH Publications AS
(
SELECT publicationId AS PublicationID, 'P' AS Rowtype
FROM dbo.PublicationOwner 
WHERE ownerId = @ownerid

UNION ALL

--Note, I am not using the AreaBuy table, because the info is in PublicationAreaBuy
SELECT a.AreaBuyID, 'A' AS Rowtype
FROM Publications AS p
JOIN PublicationAreaBuy AS a
ON a.PublicationID = p.PublicationID
)
SELECT *
FROM Publications
ORDER BY PublicationID
;

And... doing a join will stop any problems with NULLs in your IN clause.

Edit: This next query will work in versions prior to SQL 2005

DECLARE @ownerid INT
SET @ownerid = 31331

SELECT publicationId AS PublicationID, 'P' AS Rowtype
FROM dbo.PublicationOwner 
WHERE ownerId = @ownerid

UNION ALL

--Note, I am not using the AreaBuy table, because the info is in PublicationAreaBuy
SELECT a.AreaBuyID, 'A' AS Rowtype
FROM (
      SELECT publicationId AS PublicationID, 'P' AS Rowtype
      FROM dbo.PublicationOwner 
      WHERE ownerId = @ownerid
   ) AS p
JOIN PublicationAreaBuy AS a
ON a.PublicationID = p.PublicationID

ORDER BY PublicationID