Set Difference (I think) Join

[Jason]

I needed to get a list of rows from a table that is not present in
another table.

My problem lies in the fact that I only want SOME of the rows in table
2 used in determining existance. This happens because table 2 contains
historical data (based on report date). Table 1 contains my production
list.

I am able to get most of the code done but cannot seem to incorporate
the reportdate column.

Based on the code below my output should be as follows:
ReportDate = '20031229'
Output = 0 rows

ReportDate = '20031230'
Output = DIA, 123456789

ReportDate = anything else
Output = QQQ, abcdefghi
DIA, 123456789

CREATE TABLE [Indices] (
[Symbol] VARCHAR(10),
[Identity] VARCHAR(10)
)

CREATE TABLE [ClearingIndices] (
[ReportDate] DATETIME,
[Symbol] VARCHAR(10),
[Identity] VARCHAR(10)
)
GO

INSERT INTO [Indices] VALUES ('QQQ', '123456789')
INSERT INTO [Indices] VALUES ('DIA', 'abcdefghi')

INSERT INTO [ClearingIndices] VALUES ('20031229', 'QQQ', '123456789')
INSERT INTO [ClearingIndices] VALUES ('20031229', 'DIA', 'abcdefghi')
INSERT INTO [ClearingIndices] VALUES ('20031230', 'QQQ', 'abcdefghi')
GO

SELECT
I.[Symbol], I.[Identity]
FROM
[Indices] I
LEFT OUTER JOIN
ClearingIndices CI
ON
CI.[Symbol] = I.[Symbol]
AND CI.[Identity] = I.[Identity]
WHERE
--CI.[ReportDate] = '20031230'
CI.[Symbol] IS NULL
AND CI.[Identity] IS NULL

[David Portas]

Unfortunately your required output didn't match your sample data but I think
this is what you are looking for:

SELECT I.symbol, I.[identity]
FROM Indices AS I
LEFT JOIN ClearingIndices AS CI
ON CI.symbol = I.symbol
AND CI.[identity] = I.[identity]
AND CI.reportdate = '20031230'
WHERE CI.symbol IS NULL

Avoid using reserved words like "Identity" as identifiers.

--
David Portas
------------
Please reply only to the newsgroup
--