tags:

views:

101

answers:

4

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.

A: 

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.

dpmattingly
Interesting take on the question. However, when returned how would you know if you had a publicationID or an AreaBuyID in the first column? Same goes for the name column. How do you know if you have the name of an area or a publication?
Kibbee
You could add a third column if this information is necessary, or append "Publication" or "Buy" to the value of the first column. If this information is necessary, the two separate queries UNIONed together give you the flexibilty to do this easily.
dpmattingly
A: 

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.

Kibbee
A: 

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
Cade Roux
Is there a way of doing it if there is a different number of columns that I want from Publication than from AreaBuy? I need like 8 columns from publication and only two from AreaBuy, but it is saying that number of columns from each table must match.Thanks.
flavour404
This is a clear sign that UNIONing the two sets of different types of data probably makes no real sense. However, if you still want to do it, just add some NULL as whatever columns to the Area section of the query.
Cade Roux
My results set has gone up from 471 to 2129 if I incorporate the above. This is the original query.SELECT dbo.Publication.PublicationID, dbo.Publication.NameAbbrev, dbo.Publication.TestingStage, dbo.Publication.PublisherID, dbo.Publication.CountryCodes, dbo.Business.City, dbo.Business.State, dbo.Publication.Type, dbo.Publication.LastCoverPageLinkFROM dbo.Publication LEFT OUTER JOIN dbo.Business ON dbo.Publication.PublicationID = dbo.Business.BusinessIDWHERE (dbo.Publication.HasTearSheets = 1)ORDER BY dbo.Publication.NameAbbrev
flavour404
How should I incorporate the above, the new query is too long to add as a comment, ugh.
flavour404
Ok, I realised the problem, the WHERE (Publication_1.HasTearSheets = 1) is not being incorporated. How do you incorporate a 'where' clause into the query?
flavour404
I'm not following this as it is written in the comments. If you are no longer trying the UNION technique, please either amend the question or ask a new question. I have no idea why the where clause is not being obeyed.
Cade Roux
A: 

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.

flavour404