What I'm trying to do here is to select all contract_id that does not exist in the prev_contract_id column and whereby the end date is earlier than a date.
Here is my database table called contract, and the values are in (contract id, contract type, start date, end date, prev contract id, staff id) columns
insert into contract values('co-0000001', 'Monthly', '2014-01-01', '2014-02-01','null', 'staff3');
insert into contract values('co-0000002', 'Yearly', '2013-07-07', '2014-07-06','null', 'staff3');
insert into contract values('co-0000003', 'Monthly', '2014-03-20', '2014-04-19','co-0000001', 'staff4');
insert into contract values('co-0000004', 'Yearly', '2014-02-27', '2015-03-27','null', 'staff4');
insert into contract values('co-0000005', 'Ad-hoc', '2014-02-27', '2015-03-27','null', 'staff4');
insert into contract values('co-0000006', 'Yearly', '2013-02-27', '2014-02-27','null', 'staff4');
Here is my SQL statement
select contract_id from contract
where not exists(select prev_contract_id from contract where prev_contract_id <> 'null')
and end_date < '2014-05-05';
The results of each individual select statements
select prev_contract_id from contract where prev_contract_id <> 'null';
returns me co-0000001 contract_id, and
select contract_id from contract where end_date < '2014-05-05';
returns me co-0000001, co-0000003, co-0000006. I want to eliminate co-0000001 from the selection and only return me co-0000003 and co-0000006.
It works when i run them separately, but when i combine them together using the NOT EXISTS clause, I was not able to get my results. I do not want to use IN as I heard it's more time consuming or inefficient.
You are using the not exists operator wrong. This operator means that for every row in the outer select statement, the inner select is evaluated, and only if it returns no rows the row from the outer query is returned.
If you examine the inner query you're using:
select prev_contract_id from contract where prev_contract_id <> 'null'
You will see that it has nothing to do with the outer query. Since there exists rows where prev_contract_id <> 'null', no row from the outer query is ever returned.
What you're missing is an additional condition that ties the inner query with the contract_id of the outer one:
SELECT contract_id
FROM contract a
WHERE NOT EXISTS(SELECT prev_contract_id
FROM contract b
WHERE b.prev_contract_id <> 'null' AND
b.prev_contract_id = a.contract_id -- The missing relationship
) AND
end_date < '2014-05-05';
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments