Given these tables
table Channel
--------------
ChannelID int IDENTITY
<other irrelevant stuff>
table P开发者_StackOverflow中文版rogram
--------------
ProgramID int IDENTITY
ChannelID int
AiringDate datetime
<other irrelevant stuff>
and this query
SELECT C.ChannelID, t.AiringDate
FROM
Channel C
LEFT JOIN (
SELECT distinct ChannelID
FROM Program
WHERE AiringDate = '2010-01-16'
) p
ON p.ChannelID=C.ChannelID
CROSS JOIN (
SELECT AiringDate = '2010-01-16'
) t
WHERE C.ChannelID IN (1, 2, 74, 15, 906) /* the Channel table contains more channels than we are interested in */
AND p.ChannelID IS NULL
which yields
ChannelID | AiringDate
----------|-----------
2 | 2010-01-16
906 | 2010-01-16
how can I modify it to accept a date range, so that the result will be something like
ChannelID | AiringDate
----------|-----------
2 | 2010-01-16
906 | 2010-01-16
2 | 2010-01-17
906 | 2010-01-17
if there were no programs aired on these 2 channels any of these two days
This returns no rows
SELECT C.ChannelID, t.AiringDate
FROM
Channel C
LEFT JOIN (
SELECT distinct ChannelID, AiringDate
FROM Program
WHERE AiringDate between '2010-01-16' and '2010-01-17'
) p
ON p.ChannelID=C.ChannelID
CROSS JOIN (
SELECT AiringDate = '2010-01-16'
union
SELECT AiringDate = '2010-01-17'
) t
WHERE C.ChannelID IN (1, 2, 74, 15, 906)
AND p.ChannelID IS NULL
That CROSS JOIN
is a bit ugly, and it would be nice to get rid of it altogether. Note that the first example query is a bit convoluted; For single dates I have a simpler one that only outputs missing ChannelIDs:
SELECT C.ChannelID
FROM
Channel C
LEFT JOIN (
SELECT distinct ChannelID
FROM Program
WHERE AiringDate = '2010-01-16'
) p
ON p.ChannelID=C.ChannelID
WHERE C.ChannelID IN (1, 2, 74, 15, 906)
AND p.ChannelID IS NULL
If I understand correct, this should get what you requested.
- Combine all Channels with every AiringDate
- Select all Channels having AiringDates between the dates you need.
LEFT JOIN
to remove all Channels already having AiringDates
SQL Statement
DECLARE @Channel TABLE (ChannelID INTEGER PRIMARY KEY)
DECLARE @Program TABLE (ProgramID INTEGER PRIMARY KEY, ChannelID INTEGER, AiringDate DATETIME)
INSERT INTO @Channel VALUES (1) -- Aired on 16 & 17
INSERT INTO @Channel VALUES (2) -- Aired on 16
INSERT INTO @Channel VALUES (3) -- Not Aired
INSERT INTO @Program VALUES (1, 1, '01-16-2010')
INSERT INTO @Program VALUES (2, 1, '01-17-2010')
INSERT INTO @Program VALUES (3, 2, '01-16-2010')
SELECT C.*
FROM (
SELECT C.ChannelID
, p.AiringDate
FROM @Channel C
CROSS JOIN (
SELECT DISTINCT AiringDate
FROM @Program
WHERE AiringDate BETWEEN '01-16-2010' AND '01-17-2010'
) p
) c
LEFT OUTER JOIN (
SELECT C.ChannelID, p.AiringDate
FROM @Channel C
INNER JOIN @Program p ON p.ChannelID = C.ChannelID
WHERE AiringDate BETWEEN '01-16-2010' AND '01-17-2010'
) p ON p.ChannelID = C.ChannelID AND p.AiringDate = C.AiringDate
WHERE p.ChannelID IS NULL AND p.AiringDate IS NULL
精彩评论