teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問複数で、階層を持ったテーブルのjoinがうまくいかない。

編集履歴

質問編集履歴

7

PK修正

2019/06/12 04:40

投稿

退会済みユーザー
title CHANGED
File without changes
body CHANGED
@@ -25,13 +25,13 @@
25
25
 
26
26
  create table 定期商品(
27
27
  定期No varchar(12) PRIMARY KEY,
28
- 定期商品No int PRIMARY KEY,
28
+ 定期商品No int not NULL,
29
29
  商品No varchar(11)
30
30
  );
31
31
 
32
32
  create table 定期履歴(
33
33
  定期No varchar(12) PRIMARY KEY,
34
- 定期履歴連番 int PRIMARY KEY,
34
+ 定期履歴連番 int not NULL,
35
35
  解約フラグ int,
36
36
  定期開始日 datetime,
37
37
  定期終了日 datetime

6

;を追加

2019/06/12 04:40

投稿

退会済みユーザー
title CHANGED
File without changes
body CHANGED
@@ -4,30 +4,30 @@
4
4
  ユーザーCD varchar(12) PRIMARY KEY,
5
5
  氏名 varchar(40) not NULL,
6
6
  電話番号 varchar(20) not NULL
7
- )
7
+ );
8
8
 
9
9
  create table 商品(
10
10
  商品No varchar(11) PRIMARY KEY,
11
11
  ユーザーCD varchar(12) ,
12
12
  購入日 datetime
13
- )
13
+ );
14
14
 
15
15
  create table 識別(
16
16
  識別No varchar(20) PRIMARY KEY,
17
17
  商品CD varchar(7) ,
18
18
  商品No varchar(11)
19
- )
19
+ );
20
20
 
21
21
  create table 定期(
22
22
  定期No varchar(12) PRIMARY KEY,
23
23
  ユーザーCD varchar(12)
24
- )
24
+ );
25
25
 
26
26
  create table 定期商品(
27
27
  定期No varchar(12) PRIMARY KEY,
28
28
  定期商品No int PRIMARY KEY,
29
29
  商品No varchar(11)
30
- )
30
+ );
31
31
 
32
32
  create table 定期履歴(
33
33
  定期No varchar(12) PRIMARY KEY,
@@ -35,27 +35,27 @@
35
35
  解約フラグ int,
36
36
  定期開始日 datetime,
37
37
  定期終了日 datetime
38
- )
38
+ );
39
39
  ```
40
40
  ```SQL
41
- insert into ユーザー values ('100000000001','テスト太郎1','09011112222')
41
+ insert into ユーザー values ('100000000001','テスト太郎1','09011112222');
42
- insert into ユーザー values ('100000000002','テスト太郎2','09111112222')
42
+ insert into ユーザー values ('100000000002','テスト太郎2','09111112222');
43
43
 
44
- insert into 商品 values ('11111111111','100000000001','2018-12-11')
44
+ insert into 商品 values ('11111111111','100000000001','2018-12-11');
45
- insert into 商品 values ('11111111112','100000000002','2018-12-12')
45
+ insert into 商品 values ('11111111112','100000000002','2018-12-12');
46
46
 
47
- insert into 識別 values ('12345678901234567890','MKN1111','11111111111')
47
+ insert into 識別 values ('12345678901234567890','MKN1111','11111111111');
48
- insert into 識別 values ('12345678901234567891','MKN1111','11111111111')
48
+ insert into 識別 values ('12345678901234567891','MKN1111','11111111111');
49
- insert into 識別 values ('12345678901234567892','MKN1111','11111111112')
49
+ insert into 識別 values ('12345678901234567892','MKN1111','11111111112');
50
50
 
51
- insert into 定期 values ('555555555555','100000000001')
51
+ insert into 定期 values ('555555555555','100000000001');
52
52
 
53
- insert into 定期商品 values ('555555555555','1','11111111111')
53
+ insert into 定期商品 values ('555555555555','1','11111111111');
54
54
 
55
- insert into 定期履歴 values ('555555555555','1','0','2016/1/1','2016/12/31')
55
+ insert into 定期履歴 values ('555555555555','1','0','2016/1/1','2016/12/31');
56
- insert into 定期履歴 values ('555555555555','2','0','2017/1/1','2017/12/31')
56
+ insert into 定期履歴 values ('555555555555','2','0','2017/1/1','2017/12/31');
57
- insert into 定期履歴 values ('555555555555','3','0','2018/1/1','2018/12/31')
57
+ insert into 定期履歴 values ('555555555555','3','0','2018/1/1','2018/12/31');
58
- insert into 定期履歴 values ('555555555555','4','0','2019/1/1','2019/12/31')
58
+ insert into 定期履歴 values ('555555555555','4','0','2019/1/1','2019/12/31');
59
59
  ```
60
60
 
61
61
  ※テスト太郎1の持つ商品に付いている定期は、定期終了日が2019/12/31で解約フラグ0なので有効な定期がある。

5

指摘を反映

2019/06/12 04:33

投稿

退会済みユーザー
title CHANGED
File without changes
body CHANGED
@@ -1,115 +1,37 @@
1
1
  ◎テーブルの説明
2
- ○「ユーザー」テーブル
3
- ・ユーザーCD(PK)
4
- ・氏名
5
- ・電話番号
6
- などなど・・。
7
-
8
- ○「商品」テーブル
9
- ・商品No(PK)
10
- ・ユーザーCD
11
- ・購入日
12
- などなど・・。
13
-
14
- ○「識別」テーブル
15
- ・識別No(PK)
16
- ・商品No
17
-
18
- ※「商品」テーブルの下の階層。
19
- 例：「りんごパック」という商品があり、３つのリンゴがあって、それぞれに識別Noが振られる。
20
-
21
- ○「定期」テーブル
22
- ・定期No(PK)
23
- ・ユーザーCD
24
-
25
- ※定期購入を管理するテーブル
26
-
27
- ○「定期商品」テーブル
28
- ・定期No(PK)
29
- ・定期商品No(PK)
30
- ・商品No
31
-
32
- ※「商品」テーブルと「定期」テーブルを結合するためのテーブル
33
-
34
- ○「定期履歴」テーブル
35
- ・定期No(PK)
36
- ・定期履歴連番(PK)
37
- ・解約フラグ
38
- ・定期開始日
39
- ・定期終了日
40
-
41
- ※「定期」テーブルの下の階層。
42
- 例
43
- 定期No、定期履歴連番、定期開始日、定期終了日
44
- 1,1,2016/1/1,2016/12/31
45
- 1,2,2017/1/1,2017/12/31
46
- 1,3,2018/1/1,2018/12/31
47
- 1,4,2019/1/1,2019/12/31
48
-
49
- ◎やりたい抽出
50
- 商品を持つユーザーについて、「定期」が無いか、切れているユーザーを抽出したい。
51
-
52
- 「定期」が無いとは・・・そのユーザーについて「定期」テーブルのデータが無い。
53
- 「定期」が切れているとは・・・「定期履歴」の解約フラグが1、または定期終了日がシステム日付を過ぎている。
54
-
55
- ◎途中経過
56
-
57
- 複雑な構造なので、joinするとユーザがタブったりします。
58
- しかも、「定期履歴」テーブルについては定期履歴連番がMAXのデータを見る必要があります。
59
- 例でいうと、連番4の2019/1/1～2019/12/31定期のデータでみたい。
60
-
61
- select *
62
- from [商品]
63
- INNER JOIN [ユーザー] on [ユーザー].[ユーザーCD] = [商品].[ユーザーCD]
64
- INNER JOIN [識別] on [識別].[定期No] = [商品].[定期No]
65
- LEFT JOIN [定期] on [定期].[ユーザーCD] = [ユーザー].[ユーザーCD]
66
- LEFT JOIN [定期商品] on [商品].[定期No] = [定期商品].[定期No]
67
- INNER JOIN [定期履歴] on [定期履歴].[定期No] = [定期].[定期No]
68
-
69
- WHERE
70
-
71
- AND ([定期終了日] < 'システム日付' OR [解約フラグ]=1 or [定期].[定期No] IS NULL )
72
- AND [定期履歴連番] = (SELECT MAX([定期履歴連番]) from [定期履歴] WHERE [定期].[定期No] = [定期履歴].[定期No]
73
-
74
- 結合のキーが足りないのか、LEFT JOINの使い方が違うのか、なかなかうまくいきません。
75
- うまくいく方法が分かりましたら、教えていただけないでしょうか。
76
- よろしくお願いします。
77
-
78
- ◎補足をうけ、クリエイト文とインサート文
79
-
80
2
  ```SQL
81
3
  create table ユーザー(
82
- ユーザーCD varchar(12) not NULL,
4
+ ユーザーCD varchar(12) PRIMARY KEY,
83
5
  氏名 varchar(40) not NULL,
84
6
  電話番号 varchar(20) not NULL
85
7
  )
86
8
 
87
9
  create table 商品(
88
- 商品No varchar(11) not NULL,
10
+ 商品No varchar(11) PRIMARY KEY,
89
11
  ユーザーCD varchar(12) ,
90
12
  購入日 datetime
91
13
  )
92
14
 
93
15
  create table 識別(
94
- 識別No varchar(20) not NULL,
16
+ 識別No varchar(20) PRIMARY KEY,
95
17
  商品CD varchar(7) ,
96
18
  商品No varchar(11)
97
19
  )
98
20
 
99
21
  create table 定期(
100
- 定期No varchar(12) not NULL,
22
+ 定期No varchar(12) PRIMARY KEY,
101
23
  ユーザーCD varchar(12)
102
24
  )
103
25
 
104
26
  create table 定期商品(
105
- 定期No varchar(12) not NULL,
27
+ 定期No varchar(12) PRIMARY KEY,
106
- 定期商品No int not NULL,
28
+ 定期商品No int PRIMARY KEY,
107
29
  商品No varchar(11)
108
30
  )
109
31
 
110
32
  create table 定期履歴(
111
- 定期No varchar(12) not NULL,
33
+ 定期No varchar(12) PRIMARY KEY,
112
- 定期履歴連番 int not NULL,
34
+ 定期履歴連番 int PRIMARY KEY,
113
35
  解約フラグ int,
114
36
  定期開始日 datetime,
115
37
  定期終了日 datetime
@@ -138,4 +60,43 @@
138
60
 
139
61
  ※テスト太郎1の持つ商品に付いている定期は、定期終了日が2019/12/31で解約フラグ0なので有効な定期がある。
140
62
  定期履歴連番3で止まっていたり、定期履歴連番4があっても解約フラグ1なら定期が切れている。
141
- また、テスト太郎2の持つ商品には定期が付いていない。
63
+ また、テスト太郎2の持つ商品には定期が付いていない。
64
+
65
+ ※「定期履歴」テーブルは「定期」テーブルの下の階層。
66
+ 例
67
+ 定期No、定期履歴連番、定期開始日、定期終了日
68
+ 1,1,2016/1/1,2016/12/31
69
+ 1,2,2017/1/1,2017/12/31
70
+ 1,3,2018/1/1,2018/12/31
71
+ 1,4,2019/1/1,2019/12/31
72
+
73
+ ◎やりたい抽出
74
+ 商品を持つユーザーについて、「定期」が無いか、切れているユーザーを抽出したい。
75
+
76
+ 「定期」が無いとは・・・そのユーザーについて「定期」テーブルのデータが無い。
77
+ 「定期」が切れているとは・・・「定期履歴」の解約フラグが1、または定期終了日がシステム日付を過ぎている。
78
+
79
+ ◎途中経過
80
+
81
+ 複雑な構造なので、joinするとユーザがタブったりします。
82
+ しかも、「定期履歴」テーブルについては定期履歴連番がMAXのデータを見る必要があります。
83
+ 例でいうと、連番4の2019/1/1～2019/12/31定期のデータでみたい。
84
+
85
+ ```SQL
86
+ select *
87
+ from [商品]
88
+ INNER JOIN [ユーザー] on [ユーザー].[ユーザーCD] = [商品].[ユーザーCD]
89
+ INNER JOIN [識別] on [識別].[定期No] = [商品].[定期No]
90
+ LEFT JOIN [定期] on [定期].[ユーザーCD] = [ユーザー].[ユーザーCD]
91
+ LEFT JOIN [定期商品] on [商品].[定期No] = [定期商品].[定期No]
92
+ INNER JOIN [定期履歴] on [定期履歴].[定期No] = [定期].[定期No]
93
+
94
+ WHERE
95
+
96
+ AND ([定期終了日] < 'システム日付' OR [解約フラグ]=1 or [定期].[定期No] IS NULL )
97
+ AND [定期履歴連番] = (SELECT MAX([定期履歴連番]) from [定期履歴] WHERE [定期].[定期No] = [定期履歴].[定期No]
98
+ ```
99
+
100
+ 結合のキーが足りないのか、LEFT JOINの使い方が違うのか、なかなかうまくいきません。
101
+ うまくいく方法が分かりましたら、教えていただけないでしょうか。
102
+ よろしくお願いします。

4

書き方を変更

2019/06/12 00:42

投稿

退会済みユーザー
title CHANGED
File without changes
body CHANGED
@@ -77,6 +77,7 @@
77
77
 
78
78
  ◎補足をうけ、クリエイト文とインサート文
79
79
 
80
+ ```SQL
80
81
  create table ユーザー(
81
82
  ユーザーCD varchar(12) not NULL,
82
83
  氏名 varchar(40) not NULL,
@@ -113,7 +114,8 @@
113
114
  定期開始日 datetime,
114
115
  定期終了日 datetime
115
116
  )
116
-
117
+ ```
118
+ ```SQL
117
119
  insert into ユーザー values ('100000000001','テスト太郎1','09011112222')
118
120
  insert into ユーザー values ('100000000002','テスト太郎2','09111112222')
119
121
 
@@ -132,6 +134,7 @@
132
134
  insert into 定期履歴 values ('555555555555','2','0','2017/1/1','2017/12/31')
133
135
  insert into 定期履歴 values ('555555555555','3','0','2018/1/1','2018/12/31')
134
136
  insert into 定期履歴 values ('555555555555','4','0','2019/1/1','2019/12/31')
137
+ ```
135
138
 
136
139
  ※テスト太郎1の持つ商品に付いている定期は、定期終了日が2019/12/31で解約フラグ0なので有効な定期がある。
137
140
  定期履歴連番3で止まっていたり、定期履歴連番4があっても解約フラグ1なら定期が切れている。

3

簡単な補足を追加

2019/06/11 02:52

投稿

退会済みユーザー
title CHANGED
File without changes
body CHANGED
@@ -131,4 +131,8 @@
131
131
  insert into 定期履歴 values ('555555555555','1','0','2016/1/1','2016/12/31')
132
132
  insert into 定期履歴 values ('555555555555','2','0','2017/1/1','2017/12/31')
133
133
  insert into 定期履歴 values ('555555555555','3','0','2018/1/1','2018/12/31')
134
- insert into 定期履歴 values ('555555555555','4','0','2019/1/1','2019/12/31')
134
+ insert into 定期履歴 values ('555555555555','4','0','2019/1/1','2019/12/31')
135
+
136
+ ※テスト太郎1の持つ商品に付いている定期は、定期終了日が2019/12/31で解約フラグ0なので有効な定期がある。
137
+ 定期履歴連番3で止まっていたり、定期履歴連番4があっても解約フラグ1なら定期が切れている。
138
+ また、テスト太郎2の持つ商品には定期が付いていない。

2

インサート文も追加

2019/06/11 02:24

投稿

退会済みユーザー
title CHANGED
File without changes
body CHANGED
@@ -112,4 +112,23 @@
112
112
  解約フラグ int,
113
113
  定期開始日 datetime,
114
114
  定期終了日 datetime
115
- )
115
+ )
116
+
117
+ insert into ユーザー values ('100000000001','テスト太郎1','09011112222')
118
+ insert into ユーザー values ('100000000002','テスト太郎2','09111112222')
119
+
120
+ insert into 商品 values ('11111111111','100000000001','2018-12-11')
121
+ insert into 商品 values ('11111111112','100000000002','2018-12-12')
122
+
123
+ insert into 識別 values ('12345678901234567890','MKN1111','11111111111')
124
+ insert into 識別 values ('12345678901234567891','MKN1111','11111111111')
125
+ insert into 識別 values ('12345678901234567892','MKN1111','11111111112')
126
+
127
+ insert into 定期 values ('555555555555','100000000001')
128
+
129
+ insert into 定期商品 values ('555555555555','1','11111111111')
130
+
131
+ insert into 定期履歴 values ('555555555555','1','0','2016/1/1','2016/12/31')
132
+ insert into 定期履歴 values ('555555555555','2','0','2017/1/1','2017/12/31')
133
+ insert into 定期履歴 values ('555555555555','3','0','2018/1/1','2018/12/31')
134
+ insert into 定期履歴 values ('555555555555','4','0','2019/1/1','2019/12/31')

1

create文を取り敢えず追加

2019/06/11 02:13

投稿

退会済みユーザー
title CHANGED
File without changes
body CHANGED
@@ -73,4 +73,43 @@
73
73
 
74
74
  結合のキーが足りないのか、LEFT JOINの使い方が違うのか、なかなかうまくいきません。
75
75
  うまくいく方法が分かりましたら、教えていただけないでしょうか。
76
- よろしくお願いします。
76
+ よろしくお願いします。
77
+
78
+ ◎補足をうけ、クリエイト文とインサート文
79
+
80
+ create table ユーザー(
81
+ ユーザーCD varchar(12) not NULL,
82
+ 氏名 varchar(40) not NULL,
83
+ 電話番号 varchar(20) not NULL
84
+ )
85
+
86
+ create table 商品(
87
+ 商品No varchar(11) not NULL,
88
+ ユーザーCD varchar(12) ,
89
+ 購入日 datetime
90
+ )
91
+
92
+ create table 識別(
93
+ 識別No varchar(20) not NULL,
94
+ 商品CD varchar(7) ,
95
+ 商品No varchar(11)
96
+ )
97
+
98
+ create table 定期(
99
+ 定期No varchar(12) not NULL,
100
+ ユーザーCD varchar(12)
101
+ )
102
+
103
+ create table 定期商品(
104
+ 定期No varchar(12) not NULL,
105
+ 定期商品No int not NULL,
106
+ 商品No varchar(11)
107
+ )
108
+
109
+ create table 定期履歴(
110
+ 定期No varchar(12) not NULL,
111
+ 定期履歴連番 int not NULL,
112
+ 解約フラグ int,
113
+ 定期開始日 datetime,
114
+ 定期終了日 datetime
115
+ )