複数で、階層を持ったテーブルのjoinがうまくいかない。

Question

◎テーブルの説明

create table ユーザー(
    ユーザーCD       varchar(12)   PRIMARY KEY,
    氏名             varchar(40)   not NULL,
    電話番号         varchar(20)   not NULL
);

create table 商品(
    商品No           varchar(11)   PRIMARY KEY,
    ユーザーCD       varchar(12)   ,
    購入日           datetime   
);

create table 識別(
    識別No           varchar(20)   PRIMARY KEY,
    商品CD           varchar(7)   ,
    商品No           varchar(11)
);

create table 定期(
    定期No           varchar(12)   PRIMARY KEY,
    ユーザーCD       varchar(12)   
);

create table 定期商品(
    定期No           varchar(12)   PRIMARY KEY,
    定期商品No       int   not NULL,
    商品No           varchar(11)
);

create table 定期履歴(
    定期No           varchar(12)   PRIMARY KEY,
    定期履歴連番      int   not NULL,
    解約フラグ        int,
    定期開始日        datetime,
    定期終了日        datetime
);

insert into ユーザー  values ('100000000001','テスト太郎1','09011112222');
insert into ユーザー  values ('100000000002','テスト太郎2','09111112222');

insert into 商品  values ('11111111111','100000000001','2018-12-11');
insert into 商品  values ('11111111112','100000000002','2018-12-12');

insert into 識別  values ('12345678901234567890','MKN1111','11111111111');
insert into 識別  values ('12345678901234567891','MKN1111','11111111111');
insert into 識別  values ('12345678901234567892','MKN1111','11111111112');

insert into 定期  values ('555555555555','100000000001');

insert into 定期商品  values ('555555555555','1','11111111111');

insert into 定期履歴  values ('555555555555','1','0','2016/1/1','2016/12/31');
insert into 定期履歴  values ('555555555555','2','0','2017/1/1','2017/12/31');
insert into 定期履歴  values ('555555555555','3','0','2018/1/1','2018/12/31');
insert into 定期履歴  values ('555555555555','4','0','2019/1/1','2019/12/31');

※テスト太郎1の持つ商品に付いている定期は、定期終了日が2019/12/31で解約フラグ0なので有効な定期がある。
定期履歴連番3で止まっていたり、定期履歴連番4があっても解約フラグ1なら定期が切れている。
また、テスト太郎2の持つ商品には定期が付いていない。

※「定期履歴」テーブルは「定期」テーブルの下の階層。
例
定期No、定期履歴連番、定期開始日、定期終了日
1,1,2016/1/1,2016/12/31
1,2,2017/1/1,2017/12/31
1,3,2018/1/1,2018/12/31
1,4,2019/1/1,2019/12/31

◎やりたい抽出
商品を持つユーザーについて、「定期」が無いか、切れているユーザーを抽出したい。

「定期」が無いとは・・・そのユーザーについて「定期」テーブルのデータが無い。
「定期」が切れているとは・・・「定期履歴」の解約フラグが1、または定期終了日がシステム日付を過ぎている。

◎途中経過

複雑な構造なので、joinするとユーザがタブったりします。
しかも、「定期履歴」テーブルについては定期履歴連番がMAXのデータを見る必要があります。
例でいうと、連番4の2019/1/1～2019/12/31定期のデータでみたい。

select *
from [商品]
INNER JOIN [ユーザー] on [ユーザー].[ユーザーCD] = [商品].[ユーザーCD]
INNER JOIN [識別] on [識別].[定期No] = [商品].[定期No]
LEFT JOIN [定期] on [定期].[ユーザーCD] = [ユーザー].[ユーザーCD]
LEFT JOIN [定期商品]  on [商品].[定期No] = [定期商品].[定期No]
INNER JOIN [定期履歴] on [定期履歴].[定期No] = [定期].[定期No]

WHERE

AND ([定期終了日] < 'システム日付' OR [解約フラグ]=1  or  [定期].[定期No] IS NULL )
AND [定期履歴連番] = (SELECT MAX([定期履歴連番]) from [定期履歴] WHERE [定期].[定期No] = [定期履歴].[定期No]

結合のキーが足りないのか、LEFT JOINの使い方が違うのか、なかなかうまくいきません。
うまくいく方法が分かりましたら、教えていただけないでしょうか。
よろしくお願いします。

Comments

[yambejp]

複雑な検証を希望する場合はcreate table/insertでサンプルを付けてください

[Orlofsky]

質問のテーブルの定義情報は、できれば CREATE TABLE に、テーブルに存在するデータをINSERT文で https://teratail.com/help#about-markdown の[コードを入力] で載せると適切な回答が付き易いです。

[退会済みユーザー]

補足に回答いたしました、よろしくお願いします。

[Orlofsky]

質問を
```SQL
CREATE TABLE ...
```
```SQL
INSERT ...
```
に修正してください。
少しは他の人の質問も見て書き方を理解してください。

[退会済みユーザー]

大変申し訳ありませんでした。対応しました。

[Orlofsky]

PRIMARY KEYはCREATE TABLEに追加、
; は省略しない。

>に修正してください。
と書いたように、最初のテーブル定義は不要だから削除。
SELECTもコードだから、 https://teratail.com/help#about-markdown の[コードを入力] に修正。

> SQL Server 入門
で一通り勉強しないと実務に入れませんよ。

[退会済みユーザー]

修正しました。ご指摘ありがとうございました。

[Orlofsky]

CREATE TABLEでPRIMARY KEY に複数のカラムがあるとエラーになりますよね？
セミコロンも抜けたままだし。

[退会済みユーザー]

何度も申し訳ありません。

[Orlofsky]

掲示版に載せる前にSQL(今回は CREATE TABLEと INSERT) を実行してエラーにならないか確認してください。

>SQL Server PRIMARY KEY 複数
でググってもいないですし。

Answer 1

結局どんな商品について定期を持たないのかの識別も必要になるでしょうし、商品を抽出するようにしてみました。
ユーザーが必要であれば、商品のユーザーCDから取得できますしね。

select *
from 商品
where not exists (
        select 1 
        from 定期 
             inner join 定期履歴 on 定期.定期No=定期履歴.定期No
             inner join 定期商品 on 定期.定期No=定期商品.定期No
        where 定期履歴連番=(
                select max(定期履歴連番) from 定期履歴 hst where 定期No=定期履歴.定期No
              )
          and 定期終了日 > getdate() and 解約フラグ=0
          and 定期商品.商品No=商品.商品No and 定期.ユーザーCD=商品.ユーザーCD
      )

Comments

[退会済みユーザー]

クエリをご提供いただきありがとうございました。ぜひ参考にさせていただきます。

Answer 2

MAXを見る必要ありますかね？

SELECT * FROM ユーザー U
WHERE
-- 商品を持つ
EXISTS (SELECT * FROM 商品 I WHERE I.ユーザーCD = U.ユーザーCD)
-- 定期がない
AND NOT EXISTS (
    SELECT * FROM 定期履歴 H
    INNER JOIN 定期 T ON T.定期No = H.定期No
    WHERE H.解約フラグ <> 1 AND H.定期終了日 >= GETDATE()
    AND T.ユーザーCD = U.ユーザーCD
)

Comments

[退会済みユーザー]

クエリをご提供いただきありがとうございました。ぜひ参考にさせていただきます。