SQL to find missing rows

From: Stephen Eyton-Jones <sjones_at_hmssoftware.ca>
Date: 4 Feb 2003 09:56:42 -0800
Message-ID: <cf410353.0302040956.2e2a90df@posting.google.com>

I'm trying to write a single SQL statement which will identify when I am missing a row in the data as follows.

I have three tables to work with, TSDETAIL,TSEXPENS and TCPROJ as part of a timesheet system.

[SQL Script to create tables and data is inline below message]

TSDETAIL is the main timesheet line containing PROJECT codes/keys (TSD_PRJ), and hours etc. My example does not contain all the columns from the table as they are not required.

TCPROJ is the project table. If the project's name (PRJ_NAME) starts with a 'P' and the value of the Account Code (PRJ_FLD3) is not null then a row must exist in the next table TSEXPENS which matches this row.

TSEXPENS is a sub table to TSDETAIL linked by the foreign key (TSE_TSD). It must have a row when the above rule is true.

Basically I would like the TSD_KEY value (1001 in the case) returned whenever the rule is true and there is no TSEXPENS row to match.

This SQL although maybe more complex than I need but comes close. I need to select the TSD_KEY as the primary part of the SQL because the application that is running expect this table first.

SELECT TSD_KEY FROM TSDETAIL WHERE TSD_KEY IN (SELECT TSE_TSD FROM TSEXPENS WHERE TSE_TSD NOT IN (
SELECT TSD_KEY FROM TSDETAIL,TCPROJ WHERE PRJ_NAME LIKE 'P%' AND PRJ_FLD3 IS NOT NULL
 AND TSD_PRJ=PRJ_KEY)) Any help would be greatly appreciated.

Steve

CREATE TABLE TCPROJ (PRJ_KEY NUMERIC(11) NOT NULL,

        PRJ_NAME VARCHAR(8) NOT NULL,
        PRJ_FLD3 VARCHAR(20) NULL,

PRIMARY KEY (PRJ_KEY)); CREATE TABLE TSDETAIL (TSD_KEY NUMERIC(11) NOT NULL,         TSD_PRJ NUMERIC(11) NULL,
PRIMARY KEY (TSD_KEY)); CREATE TABLE TSEXPENS (TSE_KEY NUMERIC(11) NOT NULL,         TSE_TSD NUMERIC(11) NOT NULL,
PRIMARY KEY (TSE_KEY)); CREATE INDEX PRJ_NAME ON TCPROJ(PRJ_NAME); CREATE INDEX TSE_TSD ON TSEXPENS(TSE_TSD);

INSERT INTO TCPROJ (PRJ_KEY,PRJ_NAME,PRJ_FLD3) VALUES(10,'P1','ABC');
INSERT INTO TCPROJ (PRJ_KEY,PRJ_NAME,PRJ_FLD3) VALUES(15,'P2',NULL);
INSERT INTO TCPROJ (PRJ_KEY,PRJ_NAME,PRJ_FLD3) VALUES(20,'A2','XYZ');

INSERT INTO TSDETAIL (TSD_KEY,TSD_PRJ) VALUES(1000,10);

INSERT INTO TSDETAIL (TSD_KEY,TSD_PRJ) VALUES(1001,10); INSERT INTO TSDETAIL (TSD_KEY,TSD_PRJ) VALUES(1002,15); INSERT INTO TSEXPENS (TSE_KEY,TSE_TSD) VALUES(1500,1000); Received on Tue Feb 04 2003 - 11:56:42 CST