Producing data from querying three tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GTN170777 - 04 Jul 2008 11:56 GMT

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

Reply to this Message

Hugo Kornelis - 05 Jul 2008 21:15 GMT

>Hi all,
>
[quoted text clipped - 24 lines]
>
>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);

Signature

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

Reply to this Message