2

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 ?

Community
  • 1
  • 1
Cataclysm
  • 7,592
  • 21
  • 74
  • 123

3 Answers3

2

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)
)
Andrey Morozov
  • 7,839
  • 5
  • 53
  • 75
1

You can try like this:

select a.id, a.user_id, a.approver_id, a.second_approver_id 
from tbl_approve_master a
where exists (select 1 from tbl_user at
              where (a.user_id  = at.id
                or a.approver_id  = at.id
                or a.second_approver_id =id)
                and (at.name like '%john%'))
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
0

Try this query. 

;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 
join cte 
    on user_id = cte.id or approver_id = cte.id or second_approver_id = cte.id
serverSentinel
  • 994
  • 6
  • 20