위는 우리가 join을 이용하여 만들어야 할 테이블이다.
시작해보자!
tb_adv 테이블을 먼저 본다
이 테이블에선 우리가 만들 테이블에 필요한 정보가 무엇이 있을까?
adv_id - 상담번호
adv_date - 상담일자를 가지고 있다.
다음 테이블을 보자.
tb_userinfo 테이블
이 테이블에선 우리가 필요한 정보
name - 학생명
loginID - 학생 id 를 가지고 있다.
그럼 이제 위 두 테이블을 하나로 join 할것인데 adv_id테이블을 기준으로 해서 outer join을 해보겠다.
outer join을 하기 위해서는 각 테이블에서 공통으로 묶을만한 정보를 가진 컬럼을 선정해서 join을 해주어야 한다.
여기서는 adv_id 테이블의 std_id 컬럼의 정보와 tb_userinfo 테이블의 loginID컬럼의 정보가 일치하는것을 확인 할 수 있다. 우리는 이것을 on std_id = loginID 이렇게 작성해야한다.
이것을 sql문으로 작성하면 이하와같이 된다.
(select * from tb_adv av left outer join tb_userinfo ui on av.std_id = ui.loginID) aa테이블 tb_adv 옆에 av와 테이블 tb_userinfo 옆 ui는 약식명을 지정한것이다
tb_adv av left (outer는 생략가능하다) join tb_userinfo ui
이뜻은 왼쪽의 테이블이 기준이되어 왼쪽 테이블의 칼럼은 모두 표시되고 오른쪽 테이블은 왼쪽테이블과 일치하는 정보는 join되어 표시되지만 나머지는 null값이 된다.이제 이렇게 join 된 테이블을 aa 라고 다시 이름을 지어줄것이고
이 테이블이 가지고 있는 현재까지 우리가 필요한 정보는
adv_id - 상담번호
adv_date - 상담일자
name - 학생명
loginID - 학생 id 이다.
새롭게 만든 aa 테이블은 우리가 필요한 정보를 모두가진 테이블이므로 이 테이블을 기준으로 잡고 다시 필요한정보를 가진 테이블과 join을 해야한다.
tb_lec_info 테이블
이 테이블은 우리가 필요한 정보
수강 강의 - lec_name
강사 id - tutor_id 를 가지고 있다.
(위 테이블에서 tutor_name이 있지만 잘못된 설계이므로 지금은 없다고 생각하고 무시한다.)
이것을 위에서 만든 aa테이블을 기준으로하여 join하면 aa테이블의 lec_id 컬럼의 정보와 현재 tb_lec_info테이블의 컬럼 정보가 일치하는것을 확인하여 join 할 수 있다.
이것을 sql문으로 작성하면 아래와 같이 된다.
(select * from tb_adv av left outer join tb_userinfo ui on av.std_id = ui.loginID) aa left join tb_lec_info li on aa.lec_id = li.lec_id이제 우리는
adv_id - 상담번호
adv_date - 상담일자
name - 학생명
loginID - 학생 id
수강 강의 - lec_name
강사 id - tutor_id 를 가지고 있는 테이블을 join으로 만들었고 이 테이블을 기준으로 tutor_id 정보와 일치하는 강사이름을 가지고 와서 join하면 끝이 난다.우리가 필요한 유저의 정보(강사이름)은 우리가 처음에 이용한 tb_userinfo 테이블에 있는데 이것을 닉네임 ui2로 지정하고 다시 이 테이블을 이용하면 된다.
위까지 만든 테이블의 모든 정보가 존재해야하니 위 테이블을 기준으로 하고
위에서 우리가 만든 테이블의 강사id와 일치하는 정보를 갖는 tb_userinfo테이블의 loginID를 join한다.
이것을 작성하면 아래와 같이 된다.
from (select * from tb_adv av left outer join tb_userinfo ui on av.std_id = ui.loginID) aa left join tb_lec_info li on aa.lec_id = li.lec_id left join tb_userinfo ui2 on aa.tut_id = ui2.loginID이제 이 테이블은 우리가 필요로하는 정보들만이 join된 테이블이다.
이 테이블은 수많은 지금까지 join한 수많은 컬럼들을 가지고 있고 그 중에서
우리가 필요한 일치된 정보들의 컬럼을 아래와 같이 전부 가지고 있게된다.adv_id - 상담번호
adv_date - 상담일자
name - 학생명
loginID - 학생 id
lec_name - 수강 강의
tutor_id - 강사 id
name - 강사 이름
이제 select로 필요한 정보들만 아래와 같이 빼오고 oder by를 이용하여 내림차순으로 순서도 정해준다.(desc는 내림차순을 표시하는것 생략하면 오름차순이 된다.)
select aa.adv_id, li.lec_name, aa.name, aa.std_id, aa.join_date, li.tutor_name, aa.tut_id from (select * from tb_adv av left outer join tb_userinfo ui on av.std_id = ui.loginID) aa left join tb_lec_info li on aa.lec_id = li.lec_id left join tb_userinfo ui2 on aa.tut_id = ui2.loginID order by aa.adv_id desc;하지만 이렇게 select를 하면 컬럼의 이름을 보기가 힘드니 아래와 같이 명확히 이름을 정해준다.
select aa.adv_id as "상담번호", li.lec_name as "수강 강의", concat(aa.name," (",aa.std_id,")") as "학생 명(ID)", aa.join_date as "상담일자", concat(li.tutor_name," (",aa.tut_id,")") as "강사(ID)" from (select * from tb_adv av left outer join tb_userinfo ui on av.std_id = ui.loginID) aa left join tb_lec_info li on aa.lec_id = li.lec_id left join tb_userinfo ui2 on aa.tut_id = ui2.loginID order by aa.adv_id desc;이제 완성이다!
지금까지 as를 생략하고 테이블의 닉네임을 정해주었는데 여기서는 as를 붙여 닉네임을 만든 이유는 만들 닉네임에 공백, 즉 뛰어쓰기가 들어가게 만들면 as를 꼭 붙여주어야하기때문이다.
그리고 concat을 이용하면 하나의 공통된 큰 닉네임을 만들어 테이블들을 묶어 줄수있다.
