Cataclysm Cataclysm - 2 months ago 8
SQL Question

How to reuse query of IN sub query?

I have below query

select a.id, a.user_id, a.approver_id, a.second_approver_id
from tbl_approve_master a
WHERE user_id in (select id from tbl_user where name like '%john%')
or approver_id in (select id from tbl_user where name like '%john%')
or second_approver_id in (select id from tbl_user where name like '%john%')


How could I reuse the query statement of
in
? I tried the accept answer of How to reuse a sub query in sql? as

with cte as (select id from tbl_user where name like '%john%')
select a.id, a.user_id, a.approver_id, a.second_approver_id
from tbl_approve_master a
WHERE user_id in cte
or approver_id in cte
or second_approver_id in cte


but this does not ok. How can I achieve it ?

Answer

Try this:

select a.id, a.user_id, a.approver_id, a.second_approver_id 
from tbl_approve_master a 
WHERE exists (
    select * 
    from tbl_user u
    where u.name like '%john%'
    and u.id in (a.user_id, a.approver_id, a.second_approver_id)
)