Incorrect syntax near the keyword 'EXISTS'.

[syedshaffee]

Hey people,
i am getting this error 'Incorrect syntax near the keyword 'EXISTS'.'
while executing this query

SELECT Contract_detail.Contract_no
FROM   Contract_detail INNER JOIN ContractRewriteHistory ON Contract_detail.Contract_no = ContractRewriteHistory.ContractNo
WHERE Contract_detail.Contract_no  NOT EXISTS
(
SELECT ContractNo FROM ContractRewriteHistory WHERE 
ContractNo<>'' AND
ContractRewriteHistory.ContractNo = Contract_detail.Contract_no
);

Please tell me what is wrong in this

[debasisdas]

try this

SELECT Contract_detail.Contract_no
FROM   Contract_detail INNER JOIN ContractRewriteHistory ON Contract_detail.Contract_no = ContractRewriteHistory.ContractNo
WHERE NOT EXISTS
(
SELECT ContractNo FROM ContractRewriteHistory WHERE 
ContractNo<>'' AND
ContractRewriteHistory.ContractNo = Contract_detail.Contract_no
);

[Rabbit]

The EXISTS and NOT EXISTS isn't a comparison against a column. You use IN or NOT IN to compare against a column.

However, you should use neither. The current query you are attempting to use will return 0 rows. If your goal is to find contracts in detail that are not in rewrite history, that is not the correct way to go about it. You should use an outer join query and filter for the nulls.

[syedshaffee]

Thanx rabbit you rock