SQL | SOVESQL 2024 🎄: Day 24. 세 명이 서로 친구인 관계 찾기

Day 24. 세 명이 서로 친구인 관계 찾기

https://solvesql.com/problems/friend-group-of-3/

WITH
  friends_of_3820_1 AS (
    SELECT
      user_a_id friend_id
    FROM edges
    WHERE user_b_id = 3820
  ),
  friends_of_3820_2 AS (
    SELECT
      user_b_id friend_id
    FROM edges
    WHERE user_a_id = 3820
  )

SELECT
  user_a_id,
  user_b_id,
  3820 user_c_id
FROM edges
WHERE
  user_a_id IN (SELECT * FROM friends_of_3820_1)
  AND user_b_id IN (SELECT * FROM friends_of_3820_1)

UNION ALL

SELECT
  user_a_id,
  3820 user_b_id,
  user_b_id user_c_id
FROM edges
WHERE
  user_a_id IN (SELECT * FROM friends_of_3820_1)
  AND user_b_id IN (SELECT * FROM friends_of_3820_2)

UNION ALL

SELECT
  3820 user_a_id,
  user_a_id user_b_id,
  user_b_id user_c_id
FROM edges
WHERE
  user_a_id IN (SELECT * FROM friends_of_3820_2)
  AND user_b_id IN (SELECT * FROM friends_of_3820_2);

❗UNION ALL 사용해서 테이블 결합하기
❗A,B,3820 & A,3820B & 3820,A,B 인 경우의 테이블 만들어서 UNION ALL로 결합
❗너무 어려워서 참고했다...
❗참고 : https://velog.io/@semoon/solvesqlSQLite-%EC%84%B8-%EB%AA%85%EC%9D%B4-%EC%84%9C%EB%A1%9C-%EC%B9%9C%EA%B5%AC%EC%9D%B8-%EA%B4%80%EA%B3%84-%EC%B0%BE%EA%B8%B0

 

 

 

 

+) WITH 절 : friends_of_3820_1

❗USER_B_ID가 3820인 USER_A_ID를 추출

 

 

 

+) WITH 절 : friends_of_3820_2

❗USER_A_ID가 3820인 USER_B_ID를 추출