I am trying to make a left join to the same table in the same query but the result is not okey, I resolved the problem with a subquery this is the wrong query:
SELECT * FROM TableA ta
LEFT JOIN TableA Lta ON ta.key = Lta.key
AND ta.state IN('C')
AND Lta.state IN ('A','E')
WHERE Lta.key is null
This is the way how I resolved
SELECT * FROM (
SELECT * FROM TableA ta
WHERE ta.state IN('C')
) AS T LEFT JOIN TableA Lta ON T.key = Lta.key
AND Lta.state in ('A','E')
WHERE Lta.key IS NULL
I am a little confused with those queries if you can give me some information related to this topic I will be so grateful to you
Thank you