SQL Adding a row into a returned row...

Question

Ok,

I have a table publication which has PublicationID, PublisherID, NameAbbrev... and this is joined to another table called AreaBuy which has an AreaBuyID field, NameAbbrev, DateCreated and CreatedBy fields. The relationship is basically that the company has signed up a group of papers (not necessarily owned by the same umbrella company) for our products. The publication and AreaBuy tables are connection via a PublicationAreaBuy table which has a 1...M...1 relationship (ingenious names right!).

My question is: I want to select a set of publications from the publication table, lets say we signed them up between the 1st day of this year up until now, but if any of those papers returned are in a AreaBuy group, I want that returned as well! The kicker is, I only want to return two columns, this would be all the publication id's and the names, so I want the AreaBuy information to be included in those two columns. So lets say I get five results in my set:

idNum | name
1       The Herald
2       L A Times  
162     L A Area Buy
230     County Enquirer
2006    Illinois Post 

So, the LA times is in the LA Area Buy, so that shows up as well - how do you do that? Incorporate one set of result info into another?

Sorry its a crappy explanation, R.

Answer 1

OK, I think I follow what you need. I think something like this should do the trick.

SELECT Publication.PublicationID, Publication.NameAbbrev
FROM Publication
UNION ALL
SELECT AreaBuy.AreaBuyID, AreaBuy.NameAbbrev
FROM AreaBuy
INNER JOIN PublicationAreaBuy
ON AreaBuy.AreaBuyID = PublicationAreaBuy.AreaBuyID
INNER JOIN Publication
ON PublicationAreaBuy.PublicationID = Publication.PublicationID

Add on the hooks for DateCreated or any other discriminant as needed.

Answer 2

Hard to say, the explanation isn't quite clear, but you definitely want to be using a join. From reading your question, this is the closest I can get to what I think you want.

SELECT P.PublicationID, P.Name
From Publication P
LEFT JOIN PublicationAreaBuy AS PAB
ON P.PublicationID = PAB.PublicationID
LEFT JOIN AreaBuy AS AB 
ON PAB.AreaBuyID = AB.AreaBuyID
WHERE P.SignedUp > @firstDayOfYear
OR Not AB.AreaBuyID is NULL

This will return the PublicationID and the publication Name for all publications signed up after the first day of the year, or if they are in the PublicationAreaBuy table.

Answer 3

This is effectively a pivot, but it's easiest to represent as the UNION of two distinct sets - the set of publications and the set of areas they are linked to. The normal UNION de-dupes the sets, so is usually a little slower. A UNION ALL will probably produce multiple areas is two publications are in the same area so you'll need a DISTINCT to stop the duplicates there if you use UNION ALL:

SELECT 'Publication' AS RowType
    ,Publication.PublicationID AS idNum
    ,Publication.NameAbbrev AS name
FROM Publication
WHERE Publication.SignedUp >= '1/1/2009'

UNION ALL

SELECT DISTINCT 'Area' AS RowType
    ,AreaBuy.AreaBuyID AS idNum
    ,AreaBuy.NameAbbrev AS name
FROM AreaBuy
INNER JOIN PublicationAreaBuy
    ON AreaBuy.AreaBuyID = PublicationAreaBuy.AreaBuyID
INNER JOIN Publication
    ON PublicationAreaBuy.PublicationID = Publication.PublicationID
WHERE Publication.SignedUp >= '1/1/2009'

or using UNION:

SELECT 'Publication' AS RowType
    ,Publication.PublicationID AS idNum
    ,Publication.NameAbbrev AS name
FROM Publication
WHERE Publication.SignedUp >= '1/1/2009'

UNION

SELECT 'Area' AS RowType
    ,AreaBuy.AreaBuyID AS idNum
    ,AreaBuy.NameAbbrev AS name
FROM AreaBuy
INNER JOIN PublicationAreaBuy
    ON AreaBuy.AreaBuyID = PublicationAreaBuy.AreaBuyID
INNER JOIN Publication
    ON PublicationAreaBuy.PublicationID = Publication.PublicationID
WHERE Publication.SignedUp >= '1/1/2009'

In a SQL dialect supporting common table expressions, you can avoid the duplication of more complex business logic by doing this:

WITH SelectedPubs AS (
    SELECT Publication.PublicationID
        ,Publication.NameAbbrev
    FROM Publication
    WHERE Publication.SignedUp >= '1/1/2009'
)
SELECT 'Publication' AS RowType
        , SelectedPubs.PublicationID AS idNum
        ,SelectedPubs.NameAbbrev AS name
FROM SelectedPubs

UNION

SELECT 'Area' AS RowType
    ,AreaBuy.AreaBuyID AS idNum
    ,AreaBuy.NameAbbrev AS name
FROM AreaBuy
INNER JOIN PublicationAreaBuy
    ON AreaBuy.AreaBuyID = PublicationAreaBuy.AreaBuyID
INNER JOIN SelectedPubs
    ON PublicationAreaBuy.PublicationID = SelectedPubs.PublicationID

Answer 4

Thanks to everybody who replied.

I realize that this is a little nerdy, but that is awesome, I had completely forgotten about unions.

Again, thanks to everybody who replied.

Have a good one, R.