Having this one:
code IN ( SELECT DISTINCT aaa.code FROM ORGHEADER AS aaa LEFT JOIN ORGRELATEDPARTY AS bbb ON aaa.oh_pk = bbb.parent WHERE aaa.oh_pk NOT IN ( SELECT fu.parent FROM ORGRELATEDPARTY fu WHERE fu.partytype = 'MNG' ) )
Reading this one: Changing IN to EXISTS in SQL
Tried to change it into “Exists”, but produced this and it did not work:
code EXISTS ( SELECT * FROM ORGHEADER AS aaa LEFT JOIN ORGRELATEDPARTY AS bbb ON aaa.oh_pk = bbb.pr_oh_parent WHERE aaa.oh_pk NOT IN ( SELECT fu.parent FROM ORGRELATEDPARTY fu WHERE fu.pr_partytype = 'MNG' ) WHERE code = DISTINCT aaa.oh_code )
The error is 3706: Syntax error: expected something between ‘=’ and ‘DISTINCT’ keyword.
Advertisement
Answer
You have already redundant code.
Inside the IN
subquery you are selecting a column from ORGHEADER
, so the LEFT
join only adds noise since it returns in any case all rows from ORGHEADER
.
Assuming that code
belongs to a table aliased as t
you can write the code with EXISTS
like this:
WHERE EXISTS ( SELECT 1 FROM ORGHEADER AS aaa WHERE aaa.oh_code = t.code AND aaa.oh_pk NOT IN ( SELECT parent FROM ORGRELATEDPARTY WHERE pr_partytype = 'MNG' ) )
Also, NOT IN
will not work if the column parent
of the table ORGRELATEDPARTY
may return null
s.