SQLパズル p93 22 大家の悩み 解法１の学習メモ

概要

SQLパズル　p93 22 大家の悩みの 解法１の学習メモです。
単純な外部結合ですね。

理解度怪しいの再復習の必要あり

要件

tenants、rentpayments,unitsテーブルを結合した上で、以下行を取得したい。
・指定した範囲内にpayment_dateがある行
・部屋と借り主の組み合わせの行。payment_dateの値はnull

SQL

DDL

drop table if exists Tenents;
CREATE TABLE Tenants
 (tenant_id INTEGER,
  unit_nbr  INTEGER,
  vacated_date DATE,
  PRIMARY KEY (tenant_id, unit_nbr));

drop table if exists Units;
CREATE TABLE Units
 (complex_id INTEGER,
  unit_nbr   INTEGER,
  PRIMARY KEY (complex_id, unit_nbr));

drop table if exists RentPayments;

CREATE TABLE RentPayments
 (tenant_id   INTEGER,
  unit_nbr   INTEGER,
  payment_date   DATE,
  PRIMARY KEY (tenant_id, unit_nbr));

--サンプルデータ
INSERT INTO Tenants VALUES(1, 1, NULL);
INSERT INTO Tenants VALUES(1, 2, NULL);
INSERT INTO Tenants VALUES(1, 3, '2007-01-01');

INSERT INTO Units VALUES(32, 1);
INSERT INTO Units VALUES(32, 2);
INSERT INTO Units VALUES(32, 3);

/* ユニット１は家賃を払っている。２は払っていない */
INSERT INTO RentPayments VALUES(1, 1, '2007-03-01');

答えのSQL

--答え
-- WHERE句は要件で取得したい行の情報
SELECT *
FROM (Units AS U1 LEFT OUTER JOIN Tenants AS T1
    ON U1.unit_nbr = T1.unit_nbr
    AND T1.vacated_date IS NULL
    AND U1.complex_id=32)
    LEFT OUTER JOIN RentPayments AS RP1
    ON(T1.tenant_id = RP1.tenant_id
    AND U1.unit_nbr = RP1.unit_nbr )
WHERE RP1.payment_date BETWEEN  '2006-03-01' AND '2009-03-07'
OR RP1.payment_date IS NULL
	;

-- 途中式

--　部屋とテナントテーブル結合
-- 部屋と借り主の組み合わせを求める
-- 今回の場合だと unit_nb=3はvadate
SELECT * 
	FROM  (Units AS U1
	LEFT OUTER JOIN Tenants AS T1
	ON U1.unit_nbr = T1.unit_nbr
    AND T1.vacated_date IS NULL
    AND U1.complex_id=32
	);


-- 家賃テーブルを結合
SELECT *
FROM (Units AS U1 LEFT OUTER JOIN Tenants AS T1
    ON U1.unit_nbr = T1.unit_nbr
    AND T1.vacated_date IS NULL
    AND U1.complex_id=32)
    LEFT OUTER JOIN RentPayments AS RP1
    ON(T1.tenant_id = RP1.tenant_id
    AND U1.unit_nbr = RP1.unit_nbr )
	;

参考

SQLパズル　p93

SQLパズル　サポートページ
https://mickindex.sakura.ne.jp/database/db_support_sqlpuzzle.html#LocalLink-p22

達人に学ぶSQL徹底指南書第２版 類似した問題無し

github差分
https://github.com/RYA234/SQL_Puzzle_Learning/commit/a85e00f955a5e2e45aa32bd5cdb6fc2012b8c14e