
Find posts in DB2 tables that does not exist
Quote:
> Say I have following table A:
> A.A index Primary key (int)
> A.B Name Char(20)
> A.C Date DateTime
> With
> SELECT B, C from A
> GROUP BY A.C
> ORDER BY A.C
> I'll get a list sorted on dates. My problem is that I want a record set
> returned based on dates that doesn't exists in the record set returned
from
> the select. I.e. the select returns:
> 1 Team1 01.02.2001
> 2 Team1 02.02.2001 <-- Missing 03.02.2001
> 3 Team1 04.02.2001
> 4 Team2 01.02.2001 <-- Missing 02.02.2001
> 5 Team2 03.02.2001
> 6 Team2 04.02.2001
> I want a recordset that returns
> Team1 03.02.2001
> Team2 02.02.2001
> Any help would be appreciated
SQL is designed to show data that you have, not data that is missing.
However, you can get the result you want by creating an additional
(temporary) table that contains one row for every date in the period you
care about. For instance, if you are interested in all of the year 2001, you
can create a table that contains each of the dates in 2001, such as:
01.01.2001
02.01.2001
03.01.2001 etc. thru 31.12.2001.
If you call that table DATE_TABLE and its only column DATE_COLUMN, you can
revise your query as follows so that it says:
SELECT B, C from A
WHERE C NOT IN (SELECT DATE_COLUMN FROM DATE_TABLE WHERE
DATE_COLUMN IS NOT NULL)
GROUP BY A.C
ORDER BY A.C
This should produce the desired results. (Don't forget the WHERE DATE_COLUMN
IS NOT NULL part of the subquery; although you should never get put nulls in
the DATE_COLUMN in the first place, if you inadvertently do put a null in
the column, the WHERE clause will keep your query from returning an empty
set.)
Rhino