luxury515
1k
2019-08-14 10:45:39
2
83

table join 방식으로 변경해주실수 있나요?


아래 쿼리가 있습니다.

나를 추천한 추천인을 조회하고 싶은데. 나와 ,추천인 모두 동일한 user  table 에 존재합니다.

그래서 결국은  user , user_recommend , recommend table을 조인을 해야 되는데. 조인을 방식을 사용안하고 

서브쿼리를 돌렸네요.ㅠㅠ.

이쿼리로 일단 조회는 됩니다. 조인방식으로 가능하면 ansi 로 변경 부탁 드립니다.

SELECT
  kr_user
FROM user
WHERE id = (SELECT
  user_id
FROM recommend
WHERE id = (SELECT
  recommend_id
FROM user_recommend
WHERE user_id = (SELECT
  id
FROM user
WHERE ex_id = 1234)));


0
0
  • 답변 2

  • Majestic
    1k
    2019-08-14 11:40:39

    with aa as (

    select 'aaa' as user_id , 'Y' as fallow_yn , 'cc' fallow_id from dual

    union ALL

    select 'bbb' as user_id , 'N' as fallow_yn ,'' fallow_id  from dual

    union all

    select 'ccc' as user_id , 'N' as fallow_yn , '' fallow_id from dual

    )

    select a.user_id , a.fallow_id 

    from aa a, aa b

    where a.user_id = b.user_id

    and a.fallow_id = b.fallow_id;


    ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

    ansi


    with aa as (

    select 'aaa' as user_id , 'Y' as fallow_yn , 'cc' fallow_id from dual

    union ALL

    select 'bbb' as user_id , 'N' as fallow_yn ,'' fallow_id  from dual

    union all

    select 'ccc' as user_id , 'N' as fallow_yn , '' fallow_id from dual

    )

    select a.user_id , a.fallow_id 

    from aa a

    join aa b

    on a.user_id = b.user_id

    where  a.fallow_id = b.fallow_id;



    대략 이런 느낌으로 하면 되지 않을까요?


    셀프조인써서 

    0
  • CyanGlint
    390
    2019-08-14 16:22:22


    SELECT a.kr_user
    FROM	user a
    	INNER JOIN recommend b on b.user_id = a.id
    	INNER JOIN user_recommend c on c.recommend_id = b.id
    	INNER JOIN user d on d.id = c.user_id
    WHERE	d.ex_id = 1234

    이렇게 해 보시져

    0
  • 로그인을 하시면 답변을 등록할 수 있습니다.