안녕하세요.. 프로젝트 혼자 진행하다가 쿼리에 막혀가지고 아무것도 못하고 있는 초보 입니다.
DB 고수님들의 도움을 받고자 이렇게 글 작성해봅니다.
본론으로 들어가면 제 테이블은 현재 이렇게 구성이 되어 있습니다.
users 테이블
user_id | nickname | password | role | |
1 | aaa@aaa.com | 사용자1 | 1234 | USER |
2 | bbb@bbb.com | 사용자2 | 1234 | USER |
3 | ccc@ccc.com | 사용자3 | 1234 | USER |
4 | ddd@ddd.com | 사용자4 | 1234 | USER |
5 | eee@eee.com | 사용자5 | 1234 | USER |
6 | fff@fff.com | 사용자6 | 1234 | USER |
7 | ggg@ggg.com | 사용자7 | 1234 | USER |
8 | hhh@hhh.com | 사용자8 | 1234 | USER |
9 | iii@iii.com | 사용자9 | 1234 | USER |
squad 테이블
squad_id | squad_explain | squad_name | category_id |
1 | 자바와 관련된 스터디 입니다. | 자바지기 | 1 |
2 | 자바 스트림에 대해서 공부합니다. | 스트리밍자바 | 2 |
3 | C언어 깊게 공부합니다. | CCTV | 3 |
category 테이블
category_id | category_name |
1 | JAVA |
2 | C |
user_squad 테이블 (users와 squad의 연결테이블 입니다.)
user_squad_id | is_creator | is_mentor | is_creator | user_id |
1 | true | true | 1 | 1 |
2 | false | false | 1 | 2 |
3 | true | false | 2 | 3 |
4 | false | false | 2 | 4 |
5 | false | false | 2 | 5 |
6 | false | true | 3 | 6 |
7 | true | false | 3 | 7 |
8 | false | false | 3 | 8 |
저는 여기서 4개 테이블을 조인해서 해당 데이터를 얻어오게 하고 싶습니다.
스쿼드 아이디 | 스쿼드 인원 | 스쿼드 이름 | 스쿼드 설명 | 카테고리 이름 | 생성자 이름 |
1 | 2 | 자바지기 | ... | ... | 사용자1 |
3 | 3 | CCTV | ... | ... | 사용자7 |
SELECT squad.squad_id, Count(us.squad_id), squad.squad_name, squad.squad_explain, category.category_id, users.nickname FROM squad JOIN user_squad us ON us.squad_id = squad.squad_id JOIN category ON category.category_id = squad.category_id LEFT JOIN users ON users.user_id = us.user_id AND us.is_creator = true LEFT JOIN user_squad mentor ON us.squad_id = mentor.squad_id AND mentor.is_mentor = true WHERE mentor.user_squad_id is NULL GROUP BY squad.squad_id, users.nickname HAVING users.nickname is not null;
근데 해당 데이터를 얻어올 때 mentor가 존재하지 않는, 존재하는 스쿼드를 조회하고 싶은데 이럴 때 스쿼드의 인원 수를 구할 수 없어 문제가 발생하고 있습니다.
이럴 때 어떻게 해야 스쿼드를 생성한 사람을 구하면서 스쿼드 인원 수를 구할 수 있을 까요?
SELECT s.squad_id , COUNT(us.squad_id) user_cnt , MIN(s.squad_name) squad_name , MIN(s.squad_explain) squad_explain , MIN(c.category_name) category_name , MIN(CASE WHEN us.is_creator = true THEN u.nickname END) creator_nickname FROM squad s LEFT OUTER JOIN category c ON s.category_id = c.category_id LEFT OUTER JOIN user_squad us ON s.squad_id = us.squad_id LEFT OUTER JOIN users u ON us.user_id = u.user_id GROUP BY s.squad_id HAVING COUNT(CASE WHEN us.is_mentor = true THEN 1 END) = 0 -- mento 가 없는 squad ;
1. 그룹바이 집계 쿼리에서는
집계 기준항목을 제외한 나머지 항목은
SELECT 절에서 그대로 사용이 불가합니다. 집계함수와 함께 사용해야 합니다.
집계 기준항목인 squad_id 를 제외한 나머지 항목들은
어차피 squad_id 에 종속되는 항목입니다.
예를 들면 squad_id 하나당 squad_name 은 한가지만 존재합니다.
MIN 을 하든 MAX 를 하든 한가지만 나오게 됩니다.
squad_id 하나당 is_creator = true 인 자료는 하나 뿐이라고 가정하고
CASE 문을 이용해 해당 자료만 MIN 으로 감싸주는 것입니다.
MIN 을 사ㅣ용하기 싫다면 집계 기준항목에 추가해 줘도 무방합니다.
GROUP BY s.squad_id, s.squad_name, s.squad_explain, c.category_name
2. INNER JOIN / OUTER JOIN 선택
선택은 필요에 의해서 합니다.
squad 테이블에 category 가 정해지지 않을 가능성이 있다면? 아우터 조인을, 반드시 정해진다면? 이너조인을 선택하면 됩니다.
squad_user 테이블에 아직 자료가 입력되기 전이라면? 아우터 조인이 필요할 것입니다. 아니면 이너조인 선택
저는 불확실성을 가정하여 아우터 조인을 사용했습니다.
확실하다면 이너조인을 선택하는 것을 권장합니다.
SELECT s.squad_id , COUNT(*) user_cnt , s.squad_name , s.squad_explain , c.category_name , MIN(CASE WHEN us.is_creator = true THEN u.nickname END) creator_nickname FROM squad s INNER JOIN category c ON s.category_id = c.category_id INNER JOIN user_squad us ON s.squad_id = us.squad_id INNER JOIN users u ON us.user_id = u.user_id GROUP BY s.squad_id, s.squad_name, s.squad_explain, c.category_name HAVING COUNT(CASE WHEN us.is_mentor = true THEN 1 END) = 0 -- mento 가 없는 squad ;