SQL MS SQL Server V.8 UNION problem

Question

Hi,

I am using this query:

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

And it returns 0 results. What I am trying to do is add in a union with my PublicationsAreaBuy table, which contains a publicationID and an ABID (area buy id). What I am trying to do is if the above query returns a publicationID that is in the area buy table then I need to include (which is why I was using the union) the area buy id as a publication id.

This was my last attempt but again it returns 1 result whether the top query returns a result or not... my mind has gone completely blank on this one!

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     AreaBuy.AreaBuyID AS PublicationID
FROM         AreaBuy INNER JOIN
                  PublicationAreaBuy ON AreaBuy.AreaBuyID =         PublicationAreaBuy.AreaBuyID INNER JOIN
                  dbo.PubAdvertiser AS PubAdvertiser_1 ON PubAdvertiser_1.PublicationID = PublicationAreaBuy.PublicationID
ORDER BY pat.PublicationID

Answer 1

I'm sorry that I'm not able to understand exactly what your issue is, but I wonder if your issue stems from the fact that the "UNION" operator will eliminate duplicate rows (kind of like a "SELECT DISTINCT" over the combined query). If you don't want this behavior, use "UNION ALL" instead.

Something else I noticed right away was this part: "(pat.LastAdDate > 7 / 1 / 2009)". It looks very much like you are trying to compare "LastAdDate" to a hard-coded date value. In reality you are comparing "LastAdDate" to the value of 7 divided by 1 divided by 2009. Since this is integer division, your expression is essentially "(pat.LastAdDate > 0)".

If you really want to compare against a hard-coded date, you need to put the date value in single quotes, like this "(pat.LastAdDate > '7/1/2009')". In a production application, hard-coded dates are a bad idea, but I assume this is just for testing/debugging purposes.