CODEWITHSUNDEEP
sqlamazon-redshift
user8834780
user8834780

Reputation: 1670

Re-writing query from in() to joins

Can you assist in re-writing this into joins?

select * from users where users.advised_by in (
select p.id 
from advisors p
join advisor_members m on p.id = m.advisor_id
join representatives r on m.user_id=r.user_id
where m.memeber_type='Advisor'
)

This is part of 200+ row query and that in() statement is hard to maintain when there are changes.

Upvotes: 2

Views: 29

Answers (2)

Rama krishna
Rama krishna

Reputation: 101

/*Option 1 */

SELECT * FROM users usr INNER JOIN ( SELECT p.id AS advisor_id FROM advisors p JOIN advisor_members m ON p.id = m.advisor_id JOIN representatives r ON m.user_id=r.user_id WHERE m.memeber_type='Advisor' ) T2 usr.advised_by = t2.advisor_id

/*Option2 -- */ SELECT * FROM users usr INNER JOIN advisors p ON usr.advised_by=p.id JOIN ( SELECT * FROM advisor_members WHERE m.memeber_type='Advisor') m ON p.id = m.advisor_id JOIN representatives r ON m.user_id=r.user_id

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

you should use a proper on clause 

select * 
from users 
inner join 
(
select p.id 
from advisors p
join advisor_members m on p.id = m.advisor_id
join representatives r on m.user_id=r.user_id
where m.memeber_type='Advisor'
) t on users.advised_by  = t.id

Upvotes: 2

Related Questions