Advanced Query Question

What I am trying to accomplish is to return a resultset that would look
like this:
Company.Name Table1.Column Table2.Column Table3.Column
Company A y y y
Company B n y n
Company C n n y
Company D n n n
I am using 4 tables to do this (Company, Table1, Table2, Table3). What
makes this somewhat complex is that I need to filter on a specific date
range (ie. 5/1/2006 - 5/31/2006) and display a company even if nothing
is available for the date range entered (Company D). Each Table has
its own date column. So I would need to apply my date criteria to
Table1.Date, Table2.Date, and Table3.Date.
In my first attempt I was using an Outer Join to display all company
names even if nothing was available in the three columns. That didn't
seem to work because then Company D wouldn't be included in the output.
The primary/foreign key relationship on the tables is the CompanyId.
The primary CompanyId being in the Company table and the foreign
CompanyId being in Tables 1,2, & 3.
How can I write this SQL statement in order to get the above results?
Thanks
*** Sent via Developersdex http://www.examnotes.net ***"JP" <jp@.nospam.com> wrote in message
news:OJoiMuahGHA.4368@.TK2MSFTNGP03.phx.gbl...
> What I am trying to accomplish is to return a resultset that would look
> like this:
> Company.Name Table1.Column Table2.Column Table3.Column
> Company A y y y
> Company B n y n
> Company C n n y
> Company D n n n
<snip>
CREATE TABLE #Company (CompanyID INT NOT NULL PRIMARY KEY,
[Name] VARCHAR(100) NOT NULL)
CREATE TABLE #Table1 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table1 ADD
CONSTRAINT FK_Table1_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
CREATE TABLE #Table2 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table2 ADD
CONSTRAINT FK_Table2_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
CREATE TABLE #Table3 (CompanyID INT NOT NULL,
EntryNum INT NOT NULL,
[Date] DATETIME,
PRIMARY KEY (CompanyID, EntryNum))
ALTER TABLE #Table3 ADD
CONSTRAINT FK_Table3_Company FOREIGN KEY
(
[CompanyID]
) REFERENCES #Company
(
[CompanyID]
)
INSERT INTO #Company (CompanyID, [Name])
SELECT 1, 'Microsoft'
UNION SELECT 2, 'Hooters'
UNION SELECT 3, 'Ca$h Munny Records'
UNION SELECT 4, 'Widgets LLC.'
UNION SELECT 5, 'Five Inc'
INSERT INTO #Table1 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-01-01'
UNION SELECT 1, 2, '2006-05-01'
UNION SELECT 3, 1, '2005-01-01'
UNION SELECT 5, 1, '2006-05-15'
INSERT INTO #Table2 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-05-31'
UNION SELECT 1, 2, '2006-01-01'
UNION SELECT 2, 1, '2005-04-01'
UNION SELECT 4, 1, '2006-05-15'
INSERT INTO #Table3 (CompanyID, EntryNum, [Date])
SELECT 1, 1, '2006-05-02'
UNION SELECT 2, 1, '2006-05-04'
UNION SELECT 3, 1, '2006-05-09'
UNION SELECT 4, 1, '2006-05-15'
UNION SELECT 5, 1, '2006-05-14'
UNION SELECT 2, 2, '2001-01-01'
SELECT DISTINCT c.CompanyID, c.[Name],
CASE WHEN t1.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table1_Column],
CASE WHEN t2.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table2_Column],
CASE WHEN t3.EntryNum IS NULL
THEN 'n'
ELSE 'y'
END AS [Table3_Column]
FROM #Company c
LEFT OUTER JOIN #Table1 t1
ON c.CompanyID = t1.CompanyID
AND (t1.[Date] >= '2006-05-01' AND t1.[Date] < '2006-06-01')
LEFT OUTER JOIN #Table2 t2
ON c.CompanyID = t2.CompanyID
AND (t2.[Date] >= '2006-05-01' AND t2.[Date] < '2006-06-01')
LEFT OUTER JOIN #Table3 t3
ON c.CompanyID = t3.CompanyID
AND (t3.[Date] >= '2006-05-01' AND t3.[Date] < '2006-06-01')
DROP TABLE #Table3
DROP TABLE #Table2
DROP TABLE #Table1
DROP TABLE #Company|||Thank you very much Mike!
*** Sent via Developersdex http://www.examnotes.net ***