MySQLの結合について

Question

以下、注文テーブルの「PR_CODE」で広告クリックテーブルと結合し、注文情報とクリック情報を取得したいのですが、PR_CODEが【000000】のように注文テーブルには無いが、広告クリックテーブルにはクリック数がある場合や、注文テーブルにはあるが広告クリックテーブルが無いといった場合にうまく取得ができませんでした。
このような場合でもSQLで取得できるのでしょうか。

バージョン：mysql5.6.36

###注文テーブル（ORDER_MST）
|ORDER_ID|PR_CODE|ORDER_PRICE|TAX
|:--:|:--:|
|10000|000001|5000|400
|10001|000001|10000|800
|10002|000002|10000|800
|10003|zzzzzz|10000|800

###広告クリックテーブル（PR_CLICK）

PR_CODE	CLICK_CNT
000000	10
000001	20
000002	30

###試したこと

SELECT 
	ORDER_MST.PR_CODE, 
	SUM(ORDER_MST.ORDER_PRICE) as ORDER_PRICE, 
	SUM(ORDER_MST.TAX) as TAX 
FROM 
	ORDER_MST 
LEFT JOIN 
	PR_CLICK 
ON 
	ORDER_MST.PR_CODE = PR_CLICK.PR_CODE
GROUP BY 
	ORDER_MST.PR_CODE

###取得したい結果

PR_CODE	ORDER_PRICE	TAX	CLICK
000000	NULL	NULL	10
000001	15000	1200	20
000002	10000	800	30
zzzzzz	10000	800	NULL

Answer 1

こんな感じです

元データ

create table ORDER_MST(ORDER_ID varchar(10),PR_CODE varchar(10),ORDER_PRICE int,TAX int);
insert into ORDER_MST values
('10000','000001',5000 ,400),
('10001','000001',10000,800),
('10002','000002',10000,800),
('10003','zzzzzz',10000,800);

create table PR_CLICK(PR_CODE varchar(10),CLICK_CNT int);
insert into PR_CLICK values
('000000',10),
('000001',20),
('000002',30);

集計

select t1.PR_CODE,t2.ORDER_PRICE,t2.TAX,t3.CLICK_CNT as CLICK
from (select PR_CODE from ORDER_MST UNION select PR_CODE from PR_CLICK) as t1
left join (select PR_CODE,SUM(ORDER_PRICE) as ORDER_PRICE,SUM(TAX) as TAX from ORDER_MST group by PR_CODE) as t2
on t1.PR_CODE=t2.PR_CODE
left join PR_CLICK as t3
on t1.PR_CODE=t3.PR_CODE
order by PR_CODE asc

Comments

[mayoi_maimai]

早急なご回答ありがとうござい枚ます。
UNIONは今まであまり使ったことが無かったので非常に勉強になりました！

Answer 2

こんな感じでしょうか。
最初に両テーブルからPR_CODEの一覧を抽出して、それにLEFT JOINで必要なデータをくっつけていくイメージです。

select * from
(
    select a.pr_code, b.order_price, b.tax, c.click_cnt from
    (
        select pr_code from order_mst
        union
        select pr_code from pr_click
    ) a
    left join
    (
        select pr_code, sum(order_price) as order_price, sum(tax) as tax from order_mst group by pr_code
    ) b on a.pr_code=b.pr_code
    left join pr_click c on a.pr_code=c.pr_code
    group by a.pr_code
) d
order by pr_code;

Comments

[mayoi_maimai]

ご回答ありがとうございました。
解説がありイメージしやすかったですm(_)m