[MySQL]join on where

select t.idx, t.name
		, r2.`member` as parent_rec
		, (select t2.name from table t2  where t2.idx = r2.`member`) as parent_name
		, r1.`target`  as child_rec
		, (select t1.name from table t1  where t1.idx = r1.`target`) as child_name
from table t
left join recommender r1
on t.idx = r1.`member` 
left join recommender r2
on t.idx = r2.target 
order by t.idx ;

SELECT m.idx,
		m.grade,
		m.login_id,
		m.login_pw,
		(SELECT o.goodsno FROM order WHERE o.idx = (SELECT idx FROM member WHERE login_id = 'test') AND o.order_status = '2' ORDER BY o.ordno DESC LIMIT 1 ) AS goodsno,
		(SELECT o.order_status FROM order WHERE o.idx = (SELECT idx FROM member WHERE login_id = 'test') AND o.order_status = '2' ORDER BY o.ordno DESC LIMIT 1 ) AS order_status
	FROM member m
	LEFT JOIN order o
	ON m.idx = o.m_idx
	WHERE m.login_id = 'ty13' ORDER BY o.ordno DESC LIMIT 1

[ON vs WHERE]

ON : JOIN 을 하기 전 필터링을 한다 ( = ON 조건으로 필터링이 된 레코들간 JOIN이 이뤄진다)

WHERE : JOIN 을 한 후 필터링을 한다 ( = JOIN을 한 결과에서 WHERE 조건절로 필터링이 이뤄진다)

참조
https://ba-gotocode131.tistory.com/32
https://yusang.tistory.com/75
https://sohyunwriter.tistory.com/41
https://blog.leocat.kr/notes/2017/07/28/sql-join-on-vs-where
https://developyo.tistory.com/121