쿼리 연습 mysql left join on 2개시 and

Mysql 쿼리를 연습하다
남들이 해놓은거 많이 봐서 잘 할줄 알았는데 아니었다. 역시... 백문이불여일타!!!!!

서브쿼리 지양하기!
처음에는 이런식으로 쿼리를 짬

SELECT  
TRMNL_ID, MNFC_NM, MDL_NM, MNFC_SRL_NO
, GTWY_ID 
, ( SELECT tb.USIM_NO 
	FROM M_GTWY_INF AS ta
	LEFT JOIN M_USIM_INF AS tb
	ON ta.USIM_ID = tb.USIM_ID
	WHERE ta.GTWY_ID = a.GTWY_ID
) AS USIM_NO -- 유심번호
, TRMNL_NM
, (
 SELECT SCN_NM FROM m_scn_inf WHERE SCN_ID = (
	 SELECT td.SCN_ID 
		FROM m_dust_trmnl_inf AS tc
		LEFT JOIN m_scn_dust_trmnl_reg_inf AS td 
		ON tc.TRMNL_ID = td.TRMNL_ID 
		WHERE tc.TRMNL_ID = a.TRMNL_ID )
 ) AS SCN_NM
, RMRK
, (
 SELECT te.CD_NM FROM m_cd_inf AS te 
		WHERE te.CD_GRP_ID = 'STS_CD' 
		AND te.CD_VAL = a.STS_CD
  ) AS STS_CD -- 상태코드
, USE_YN, REG_DT
FROM m_dust_trmnl_inf AS a
ORDER BY TRMNL_ID ASC ;

-- 혼자 피드백 : 정답 쿼리를 확인해보니
-- 서브쿼리로 사용한 SELECT WHERE 보다도
-- 메인쿼리에서 LEFT JOIN 을 하는 방향이
-- 좋을 듯함.

그리고 피드백 받고나서 서브쿼리 최대한 안함

-- 디바이스관리 -- :: 안보고 작성한 것
-- 게이트웨이ID / 펌웨어버전 / 제조사명 / 모델명 / 제조사SN / 전압 / 유심번호 / 유심사용여부 / 상태코드 / 비고 / 원시데이터저장여부 / 사용여부 / 입고일시(사용 시작 일자)
-- GTWY_ID, FW_VER, MNFC_NM, MDL_NM, GTWY_NO, CD_GTWY_CLS, 유심번호, 유심사용여부, STS_CD,RMRK,RAW_SAVE_YN,USE_YN, USE_STRT_YMD

SELECT 
  a.GTWY_ID
  , a.FW_VER
  , a.MNFC_NM
  , a.MDL_NM
  , a.GTWY_NO
  , c.CD_NM 
  , b.USIM_NO
  , b.USE_YN AS USIM_USE_YN
  , d.CD_NM 
  , a.RMRK
  , a.RAW_SAVE_YN
  , a.USE_YN
  , a.USE_STRT_YMD
FROM jbq.M_GTWY_INF AS a
LEFT JOIN jbq.M_USIM_INF AS b
ON a.USIM_ID = b.USIM_ID
LEFT JOIN jbq.M_CD_INF AS c
ON a.GTWY_CLS = c.CD_VAL
AND c.CD_GRP_ID = 'GTWY_CLS'
LEFT JOIN jbq.M_CD_INF AS d
ON a.STS_CD = d.CD_VAL
AND d.CD_GRP_ID = 'STS_CD';
-- 
-- 혼자 피드백 : 정답 쿼리를 확인해보니
-- 오늘은 어제보다 서브쿼리를 쓰지 않으려고 노력했음. 
-- 새로 알아낸 것 : LEFT JOIN 후 키를 
매칭 시킬 때, 처음에는 ON을 2번 써서 에러남.
ON 다음에는 AND로 연결하기.
-- --------------------------------------------------------------------------------------------------------------
-- 정답 찾아가는 중  ::::: 전압 GTWY_CLS / 1 : 12V, 2 : 5V ::::: 상태코드 / 공단말기-정상, 개통단말기-정상 등등
-- --------------------------------------------------------------------------------------------------------------
-- STEP 1 
SELECT CD_VAL, CD_NM FROM jbq.M_CD_INF WHERE CD_GRP_ID = 'GTWY_CLS';
-- STEP 2
SELECT  c.CD_NM 
FROM jbq.M_GTWY_INF AS a 
LEFT JOIN jbq.M_CD_INF AS c
ON a.GTWY_CLS = c.CD_VAL
WHERE c.CD_GRP_ID = 'GTWY_CLS';
-- STEP 3
SELECT  d.CD_NM 
FROM jbq.M_GTWY_INF AS a
LEFT JOIN jbq.M_CD_INF AS d
ON a.STS_CD = d.CD_VAL
WHERE d.CD_GRP_ID = 'STS_CD';

 

 

-- --------------------------------------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------------------------------------
-- 아래는 정답 쿼리
-- --------------------------------------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------------------------------------
SELECT  a.GTWY_ID,

                        a.FW_VER,

                        a.MNFC_NM,

                        a.MDL_NM,

                        a.GTWY_NO,

                        b.CD_NM AS CD_GTWY_CLS,

                        CASE LENGTH(c.USIM_NO)

                                WHEN 11 THEN CONCAT(LEFT(c.USIM_NO, 3), '-', MID(c.USIM_NO, 4, 4), '-', RIGHT(c.USIM_NO, 4))

                                WHEN 10 THEN CONCAT(LEFT(c.USIM_NO, 3), '-', MID(c.USIM_NO, 4, 3), '-', RIGHT(c.USIM_NO, 4))

                        END AS USIM_NO,

                        c.USE_YN AS USIM_USE_YN,

                        d.CD_NM AS CD_STS_CD,

                        a.GTWY_CLS,

                        a.RMRK,

                        a.RAW_SAVE_YN,

                        a.USE_YN,

                        a.USE_STRT_YMD

                FROM

                        m_gtwy_inf AS a

                LEFT JOIN

                        m_cd_inf AS b

                ON 

                        a.GTWY_CLS = b.CD_VAL AND

                        b.CD_GRP_ID = 'GTWY_CLS'

                LEFT JOIN

                        m_usim_inf AS c

                ON

                        a.USIM_ID = c.USIM_ID

                LEFT JOIN

                        m_cd_inf AS d

                ON 

                        a.STS_CD = d.CD_VAL AND

                        d.CD_GRP_ID = 'STS_CD';