Producing data from querying three tables   

Hi all,

I'm trying to run a query that produces data from three tables, in essence 
these are Location, Category and BannerPeriod, They all have a secondary key 
of SiteID and are linked by this, really there are two stages to this query,

Stage 1 produce a complete combination of all categories and locations where 
SiteID  = 'param' so for instance for a SiteID of 1 there are lets say 3 
locations - Hereford, Manchester, Leatherhead and two categories - 
Accounting, It, this part of the query would produce six records, Hereford 
Accounting, Hereford IT, Manchester Accounting etc.

Stage 2 is where it gets a little complicated, I then have a table called 
BannerPeriod, which has the following structure -
BannerID, SiteID, BannerCategory, BannerLocation, BannerCategory, 
BannerStartDate, BannerEndDate (There are other columns but these are the 
only important ones) 

What I'm trying to do, is firstly I pass two parameters to the database, 
StartDate and EndDate, based on these parameters the recordset produces a 
combined list of all categories and locations for the SiteID, where the 
category and location isn't listed in the BannerPeriod table with 
BannerStartDate Between Startdate and Enddate of a BannerEndDate between 
Startdate and Endate.

I'm really confused about this, so would be really grateful for your input..

thank you

date: Fri, 4 Jul 2008 03:56:00 -0700   author:   GTN170777

Re: Producing data from querying three tables   

On Fri, 4 Jul 2008 03:56:00 -0700, GTN170777 wrote:

>Hi all,
>
>I'm trying to run a query that produces data from three tables, in essence 
>these are Location, Category and BannerPeriod, They all have a secondary key 
>of SiteID and are linked by this, really there are two stages to this query,
>
>Stage 1 produce a complete combination of all categories and locations where 
>SiteID  = 'param' so for instance for a SiteID of 1 there are lets say 3 
>locations - Hereford, Manchester, Leatherhead and two categories - 
>Accounting, It, this part of the query would produce six records, Hereford 
>Accounting, Hereford IT, Manchester Accounting etc.
>
>Stage 2 is where it gets a little complicated, I then have a table called 
>BannerPeriod, which has the following structure -
>BannerID, SiteID, BannerCategory, BannerLocation, BannerCategory, 
>BannerStartDate, BannerEndDate (There are other columns but these are the 
>only important ones) 
>
>What I'm trying to do, is firstly I pass two parameters to the database, 
>StartDate and EndDate, based on these parameters the recordset produces a 
>combined list of all categories and locations for the SiteID, where the 
>category and location isn't listed in the BannerPeriod table with 
>BannerStartDate Between Startdate and Enddate of a BannerEndDate between 
>Startdate and Endate.
>
>I'm really confused about this, so would be really grateful for your input..
>
>thank you

Hi GTN170777,

This would be a lot easier with CREATE TABLE statements for the actual
table structure, INSERT statements with some sample data, and required
output in your post. But let's give it a shot anyway.

If I understand the requirement correctly, the query below in just one
of many ways to do it - one that follows the above description quite
closely.

SELECT      l.LocationID, c.CategoryID
FROM       (SELECT LocationID
            FROM   Location
            WHERE  SiteID      = @Param) AS l
CROSS JOIN (SELECT CategoryID
            FROM   Category
            WHERE  SiteID      = @Param) AS c
WHERE NOT EXISTS
   (SELECT  *
    FROM    BannerPeriod AS bp
    WHERE   bp.BannerLocation  = l.LocationID
    AND     bp.BannerPeriod    = c.CategoryID
    AND     bp.BannerStartDate < @EndDate
    AND     bp.BannerEndDAte   > @StartDate);

-- 
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

date: Sat, 05 Jul 2008 22:15:53 +0200   author:   Hugo Kornelis LID