Convert to union query
select t.* from table1 t where t.id NOT IN(
select Id from t2 where usrId in
(select usrId from t3 where sId=value));
I want a result that looks like there is a corresponding id in t1 and t2, then that id should be omitted and only the rest of the lines should be given to me. I tried converting to a connection, but it gives me the result I wanted. Below is my connection request.
SELECT t.* FROM table1 t JOIN table2 t2 ON t.Id <> t2.Id
JOIN table3 t3 ON t3.Id=t2.Id WHERE t3.sId= :value
It doesn't give me the correct result. it was returning all rows, but I want to restrict the result based on the match id in table t1 and table t2. The match identifier should be excluded from the result. I will pass a value for sId.
source to share
Let's break down and solve the problem step by step. So your request
select t.* from table1 t where t.id NOT IN(
select Id from t2 where usrId in
(select usrId from t3 where sId=value));
when converting inner query to JOIN will be
select t.* from table1 t where t.id NOT IN
(SELECT T2.ID FROM T2 JOIN T3 on T2.UsrID =T3.UsrID and T3.sID=value)
which upon further conversion to JOIN with external table will be
select t.* from table1 t LEFT JOIN
(SELECT T2.ID FROM T2 JOIN T3 on T2.UsrID =T3.UsrID and T3.sID=value)t4
ON t.id =T4.ID
WHERE t4.ID is NULL
If you want to remove the subquery completely, you can try like this
SELECT t.*
FROM table1 t
LEFT JOIN T2
ON T.ID=T2.ID
LEFT JOIN T3
ON T3.UsrId=T2.UsrID AND T3.sId=value
WHERE T3.UsrID IS NULL
source to share
I believe this is an exact refactoring of your query using unions. I don't know if we can do away with the subquery, but the logic looks the same anyway.
select t1.*
from table1 t1
left join
(
select t2.Id
from table2 t2
inner join table3 t3
on t2.usrId = t3.usrId
where t3.sId = <value>
) t2
on t1.Id = t2.Id
where t2.Id is null
source to share