[MySQL] Query with `Join` and `If`

다음에 보면 더 간단하게 짤 수 있지 않을까라는 생각이 들어 메모합니다.


테이블을 잠깐 설명하면


어떤 ID가 계획(부모)을 만들고 나면
다른 ID는 부모 계획을 가리키는 동일한 계획(자식)을 만들 수 있습니다.
자식계획을 만들었다고 해서 반드시 계획에 참여 하는 것은 아닙니다.
(부모 계획에 불참 가능)


(부모 자식이라는건 개념 이해를 돕기 위한것입니다.)


 User 테이블, Plan 테이블이 있고,

User 테이블에는
 `ID`와 `NM`(이름) 컬럼이 있습니다.

Plan 테이블에는 
 Primary키인 `index` 컬럼
 Unique키인 (`ID`,`code`) 컬럼
 index 컬럼을 참조하는 `plan` 컬럼
 참여를 뜻하는 일반 컬럼 `go` 가 있습니다. 


예를들어
index가 11이고 code가 1111인 부모(plan=1) 계획에 kim은 참여(go=1) 합니다.
index가 12이고 code가 1111인 자식(plan=11) 계획에 ston는 참여(go=1) 합니다.
index가 13이고 code가 1111인 자식(plan=11) 계획에 john는 불참(go=0) 합니다. 
ㅡㅡㅡㅡㅡㅡㅡㅡ
index가 20이고 code가 1111인 부모(plan=1) 계획에 poul은 참여(go=1) 합니다.
index가 21이고 code가 1111인 자식(plan=20) 계획에 nice는 참여(go=1) 합니다.


위 예제에서 index 11번이 삭제될 경우
index 12,13번도 같이 삭제시키고 Index 20 ,21 번은 삭제 안되게 하려면?
외래키 plan에 대해 Cascade로 설정하면 되겠네요.

진짜 문제는 다음과 같습니다.

클라이언트에서
kim과 1111을 가지고 쿼리했을 때
ston과 1111을 가지고 쿼리했을 때
john과 1111을 가지고 쿼리했을 때 

동일하게 이 계획에 참여하는 kim, ston의 이름을
출력해야 합니다.
(코드만 같고 다른 계획에 속한 poul,nice의 이름이나
계획은 있지만 불참하는 john의 이름은 출력하면 안됨)


다시말해
poul과 1111을 가지고 쿼리했을 때
nice와 1111을 가지고 쿼리했을 때

동일하게 poul과 nice의 이름이 출력 되어야 겠죠.






쿼리를 다음처럼 구성해보았고 결과는 잘 나오는데요
이게 과연 성능상 좋은 쿼리인지는 잘 모르겠네요.

SELECT NM
FROM Plan join `User` on `Plan`.ID=`User`.ID
WHERE ((plan=if(
(SELECT plan FROM Plan WHERE ID=? and code=?)=1,
(SELECT `index` FROM Plan WHERE ID=? and code=? ),
(SELECT `plan` FROM Plan WHERE ID=? and code=?)))
or (`index`=if(
(SELECT `plan` FROM Plan WHERE ID=? and code=?)=1,
(SELECT `index` FROM Plan WHERE ID=? and code=? ),
(SELECT `plan` FROM Plan WHERE ID=? and code=?))))
and go=1

짤땐 힘들었는데 짠다음 줄바꿈을 하고 보니 간단하네요.

쿼리를 설명하면


FROM 절 :
이름을 얻기위해 Plan과 User 테이블을 조인하는거구요


Where 절 :
1. 부모인 경우
ID와 code 로 쿼리했을 때 `plan`값이 1이겠죠.(부모) 그래서
부모의 `index`값으로 `plan`을 쿼리해 자식 계획들을 찾습니다.
근데 이러면 여기에 자식들만 포함되고 자기 자신은 포함 되지 않았죠?
그래서 or 이후 에서
plan이 1인경우 자기자신을 포함하는겁니다.

2.자식인 경우
ID와 code 로 쿼리했을 때 `plan`값이 1이 아니겠죠(자식)
그럼 자기자신을 포함한 자식들을 찾습니다.
`plan`값을 통해서요.
근데 부모가 포함되지 않았죠?
그래서 or 이후에서 1이 아닌 경우 자신의 `plan` 값으로
자신의 부모 `index` 를 찾습니다.