데이터베이스 질문과 답변
by 도뎡이 [SQL Query] mysql outer join [2022.02.15 14:06:24]
이전 글에서 답변을 받았었는데요.
핵심은 이러합니다.
=========================================
1. 가족(FAMILY) 테이블에서 나(CLIENT)를 제외한 가족(CLIENT) 데이터를 조회한다.
2. 가족(CLIENT)은 한 사람당 여러 개의 여권(PASSPORT)을 가질 수 있다.
3. 여권(PASSPORT)는 여권 코드(PASSPORT_CODE)와 1:1 관계를 가진다.
=========================================
현재, 재작성된 아래의 쿼리를 실행한 결과 데이터는 파일로 첨부하였습니다.
가족(family) 테이블에서 고객 번호(me_client_id)가 1,039번인 데이터는 총 5개가 있으며,
아래 쿼리의 결괏값도 5개가 나와야 하는 상황입니다.
하지만, 가족(other_client)이 여러 개의 여권을 가지고 있는 경우,
가장 마지막으로 INSERT 된 여권을 조회해서, 가족 테이블과 1:1로 매핑시켜 주어야 합니다.
쿼리를 어떤식으로 작성하면 좋을지 피드백 주시면 감사하겠습니다!
선배님들의 소중한 답변 미리 감사드립니다. (꾸벅)
SELECT
F.family_id -- 가족 일련번호 (Key)
, F.me_client_id -- 본인 고객 일련번호 (Key)
, F.other_client_id -- 가족 고객 일련번호 (Key)
, F.gender -- 성별
, F.relationship -- 관계
, C2.cell_phone -- 가족 휴대폰 번호
, C2.phone_number -- 가족 전화번호
, PASS.passport_id -- 여권 일련번호 (Key)
, PASS.issue_date -- 여권 발급일
, PASS.expiry_date -- 여권 만료일
, PASS.number -- 여권 번호
, PASS.status -- 여권 상태
FROM
family AS F -- 가족
INNER JOIN client AS C1 -- 본인
ON C1.client_id = F.me_client_id
INNER JOIN client AS C2 -- 상대방
ON C2.client_id = F.other_client_id
LEFT OUTER JOIN (
SELECT
P.passport_id
, P.client_id
, P.issue_date
, P.expiry_date
, P.number
, P.status
, PC.name
FROM
passport AS P -- 여권
INNER JOIN passport_code AS PC
ON P.passport_code_id = PC.passport_code_id -- 여권 코드
) AS PASS
ON F.other_client_id = PASS.client_id
WHERE
F.me_client_id = 1039
-- C1 및 PC 의 조인이 불필요 합니다.
-- 불필요한 조인은 제거하세요.
SELECT *
FROM (SELECT f.family_id -- 가족 일련번호 (Key)
, f.me_client_id -- 본인 고객 일련번호 (Key)
, f.other_client_id -- 가족 고객 일련번호 (Key)
, f.gender -- 성별
, f.relationship -- 관계
, c.cell_phone -- 가족 휴대폰 번호
, c.phone_number -- 가족 전화번호
, p.passport_id -- 여권 일련번호 (Key)
, p.issue_date -- 여권 발급일
, p.expiry_date -- 여권 만료일
, p.number -- 여권 번호
, p.status -- 여권 상태
, ROW_NUMBER() OVER(PARTITION BY F.family_id ORDER BY P.passport_id DESC) rn
FROM family f -- 가족
INNER JOIN client c -- 상대방
ON f.other_client_id = c.client_id
LEFT OUTER JOIN passport p -- 여권
ON f.other_client_id = p.client_id
WHERE f.me_client_id = 1039
) a
WHERE rn = 1
;
와...... 너무나도 훌륭한 쿼리로 피드백 주셔서 감사드립니다 선생님... (도움주실 때마다 매번 감탄을 금치 못해요 ㅠㅠ...)
1. 제가 SELECT 절에 "PASS.name" 컬럼을 빼먹었네요 ^^... PC는 기존과 동일하게 JOIN을 걸어주도록 하겠습니다!
2. ROW_NUMBER( ) OVER( )에 PARTITION BY를 사용하셨는데, 일반적인 SQL로도 저러한 표현이 가능할까요?
답변 미리 감사드립니다 :)
-- 서브쿼리 없이 하는 걸 원하시는 듯 하네요. --
SELECT f.family_id -- 가족 일련번호 (Key)
, f.me_client_id -- 본인 고객 일련번호 (Key)
, f.other_client_id -- 가족 고객 일련번호 (Key)
, f.gender -- 성별
, f.relationship -- 관계
, c.cell_phone -- 가족 휴대폰 번호
, c.phone_number -- 가족 전화번호
, p.passport_id -- 여권 일련번호 (Key)
, p.issue_date -- 여권 발급일
, p.expiry_date -- 여권 만료일
, p.number -- 여권 번호
, p.status -- 여권 상태
, pc.name -- 여권 코드명
FROM family f -- 가족
INNER JOIN client c -- 상대방
ON f.other_client_id = c.client_id
LEFT OUTER JOIN passport p -- 여권(조회용)
ON f.other_client_id = p.client_id
LEFT OUTER JOIN passport_code pc -- 여권 코드
ON P.passport_code_id = PC.passport_code_id
LEFT OUTER JOIN passport s -- 여권(비교용)
ON p.client_id = s.client_id
AND p.passport_id < s.passport_id
WHERE f.me_client_id = 1039
AND s.passport_id IS NULL
;
오늘도 소중한 답변 너무 감사드려요 마농 선생님...
선생님 덕분에 방문할 때마다 100% 확률로 문제를 해결하고 간답니다 ㅠㅠ... (정말 존경합니다...)
SQL 공부 더 열심히 해야겠습니다 ^^..
정말 감사드리고, 좋은 하루 되세요 :)
) 버튼을 클릭하여 작성 하시면 됩니다.