teratail
回答率
85.47%
質問するログイン新規登録
トップに関する質問データ抽出後にテーブルを結合、さらに絞り込みをするSQL

編集履歴

質問編集履歴

3

書式の改善

2020/12/08 06:45

投稿

Dammyyy
Dammyyy

スコア7

test CHANGED
File without changes
test CHANGED
@@ -218,15 +218,13 @@
218
218
 
219
219
  ```
220
220
 
221
- SELECT * FROM facilities_trait
221
+ SELECT * FROM facilities_trait WHERE flag = 2 AND (type = 1 OR type = 3)
222
-
223
- WHERE flag = 2 AND type IN (1, 3)
222
+
224
-
225
- AND EXISTS( SELECT 'X' FROM articles AS a WHERE r.articles_id = a.id AND a.flag = 2
223
+ AND EXISTS( SELECT 'X' FROM articles WHERE flag = 2 AND facilities_trait.trait_id = articles.id
226
-
224
+
227
- AND EXISTS( SELECT 'X' FROM rooms AS r WHERE r.id = f.rooms_id AND r.flag = 2 AND r.status = 2
225
+ AND EXISTS( SELECT 'X' FROM rooms WHERE flag = 2 AND status = 2 AND rooms.articles_id = articles.id
228
-
226
+
229
- AND EXISTS( SELECT 'X' FROM facilities_trait AS f WHERE r.id = f.rooms_id AND f.facilities_id = 31)))
227
+ AND EXISTS( SELECT 'X' FROM facilities_trait WHERE rooms.id = facilities_trait.trait_id AND facilities_trait.facilities_id = 31)))
230
228
 
231
229
  ```
232
230
 

2

質問内容の明確化

2020/12/08 06:45

投稿

Dammyyy
Dammyyy

スコア7

test CHANGED
File without changes
test CHANGED
@@ -10,11 +10,13 @@
10
10
 
11
11
  |--:|--:|--:|
12
12
 
13
- |1|2|××アパート|
13
+ |101|2|××アパート|
14
-
14
+
15
- |2|1|〇〇マンション|
15
+ |102|1|〇〇マンション|
16
-
16
+
17
- |3|2|△マンション|
17
+ |103|2|△マンション|
18
+
19
+ |:|:|:|
18
20
 
19
21
 
20
22
 
@@ -24,127 +26,201 @@
24
26
 
25
27
  |--:|--:|--:|--:|
26
28
 
27
- |1|2|2|1|
29
+ |1|2|2|101|
28
-
30
+
29
- |2|1|2|1|
31
+ |2|1|2|101|
30
-
32
+
31
- |3|2|2|2|
33
+ |3|2|2|102|
32
-
34
+
33
- |4|1|2|3|
35
+ |4|1|2|103|
34
-
36
+
35
- |5|2|1|3|
37
+ |5|2|1|103|
36
-
38
+
37
- |6|2|1|3|
39
+ |6|2|1|103|
40
+
41
+ |:|:|:|:|
38
42
 
39
43
 
40
44
 
41
45
  facilities_trait.table
42
46
 
43
- |id|flag|type|rooms_id|facilities_id|
47
+ |id|flag|type|trait_id|facilities_id|
44
-
48
+
45
- |--:|--:|--:|--:|--:|
49
+ |--:|--:|--:|--:|--:|
46
-
50
+
47
- |1|2|1|1|12|
51
+ |1|2|1|101|12|
48
-
52
+
49
- |2|2|1|1|30|
53
+ |2|2|1|101|30|
50
-
54
+
51
- |3|2|1|1|31|
55
+ |3|2|2|1|31|
52
-
56
+
53
- |4|2|1|2|12|
57
+ |4|2|1|102|12|
54
-
58
+
55
- |5|2|1|2|30|
59
+ |5|2|1|102|30|
56
-
60
+
57
- |6|2|2|3|12|
61
+ |6|2|1|103|12|
58
62
 
59
63
  |7|2|2|3|28|
60
64
 
61
- |8|2|2|3|30|
65
+ |8|2|1|103|30|
62
66
 
63
67
  |9|2|2|3|31|
64
68
 
65
- |10|2|1|4|30|
69
+ |10|2|1|104|30|
66
-
70
+
67
- |11|2|1|4|31|
71
+ |11|2|2|4|31|
68
-
72
+
69
- |12|2|3|5|25|
73
+ |12|2|3|105|25|
70
-
74
+
71
- |13|2|3|5|30|
75
+ |13|2|1|105|30|
72
-
76
+
73
- |14|2|3|5|31|
77
+ |14|2|2|5|31|
74
78
 
75
79
  |15|2|2|6|27|
76
80
 
77
- |16|2|2|6|30|
81
+ |16|2|1|106|30|
82
+
83
+
84
+
85
+ facilities_traitテーブルのtrait_idには`articles.id`と`rooms.id`が混在していて、typeでarticlesかroomsか判別しています。
78
86
 
79
87
 
80
88
 
81
89
  ## 実装したいこと
82
90
 
83
- この時、facilities_traitテーブルの`facilities_id = 31` を持つ rooms_id を全て取り出します
84
-
85
-
86
-
87
- **絞り込み後**
88
-
89
- facilities_trait.table
90
-
91
- |id|flag|type|rooms_id|facilities_id|
92
-
93
- |--:|--:|--:|--:|--:|
94
-
95
- |1|2|1|1|12|
96
-
97
- |2|2|1|1|30|
98
-
99
- |3|2|1|1|**31**|
100
-
101
- |6|2|2|3|12|
102
-
103
- |7|2|2|3|28|
104
-
105
- |8|2|2|3|30|
106
-
107
- |9|2|2|3|**31**|
108
-
109
- |10|2|1|4|30|
110
-
111
- |11|2|1|4|**31**|
112
-
113
- |12|2|3|5|25|
114
-
115
- |13|2|3|5|30|
116
-
117
- |14|2|3|5|**31**|
118
-
119
-
120
-
121
- その上で、articlesテーブルの`flag = 2`と、roomsテーブルの`flag = 2` `status = 2`、facilities_traitテーブルの`flag = 2` `type = 1 OR type = 3`の条件に合致するものを抽出したいです。
91
+
92
+
93
+ **1.**
94
+
95
+ まず、facilities_traitテーブルの`facilities_id = 31` を持つ trait_id を全て取り出します。
96
+
97
+ > facilities_trait.table
98
+
99
+ |id|flag|type|trait_id|facilities_id|
100
+
101
+ |--:|--:|--:|--:|--:|
102
+
103
+ |3|2|2|1|31|
104
+
105
+ |9|2|2|3|31|
106
+
107
+ |11|2|2|4|31|
108
+
109
+ |14|2|2|5|31|
110
+
111
+
112
+
113
+ **2.**
114
+
115
+ `type = 2`なのでroomsテーブルを見て、`facilities.trait_id = rooms.id`で紐づくものを取り出します。
116
+
117
+ > rooms.table
118
+
119
+ |id|flag|status|articles_id|
120
+
121
+ |--:|--:|--:|--:|
122
+
123
+ |1|2|2|101|
124
+
125
+ |3|2|2|102|
126
+
127
+ |4|1|2|103|
128
+
129
+ |5|2|1|103|
130
+
131
+
132
+
133
+ その際`rooms.flag = 2`,`rooms.status = 2`以外のものは除外します。
134
+
135
+ > rooms.table
136
+
137
+ |id|flag|status|articles_id|
138
+
139
+ |--:|--:|--:|--:|
140
+
141
+ |1|2|2|101|
142
+
143
+ |3|2|2|102|
144
+
145
+
146
+
147
+ **3.**
148
+
149
+ articlesテーブルで`rooms.articles_id`に紐づくものを取り出します。
150
+
151
+
152
+
153
+ > articles.table
154
+
155
+ |id|flag|title|
156
+
157
+ |--:|--:|--:|
158
+
159
+ |101|2|××アパート|
160
+
161
+ |102|1|〇〇マンション|
162
+
163
+
164
+
165
+ その際`articles.flag = 2`以外のものは除外します。
166
+
167
+ > articles.table
168
+
169
+ |id|flag|title|
170
+
171
+ |--:|--:|--:|
172
+
173
+ |101|2|××アパート|
174
+
175
+
176
+
177
+ **4.**
178
+
179
+ `articles.id`をもって改めてfacilitiesテーブルに戻り、`articles.id = facilities_trait.trait_id`を取り出します。
180
+
181
+ > facilities_trait.table
182
+
183
+ |id|flag|type|trait_id|facilities_id|
184
+
185
+ |--:|--:|--:|--:|--:|
186
+
187
+ |1|2|1|101|12|
188
+
189
+ |2|2|1|101|30|
190
+
191
+
192
+
193
+ ここで、facilities_traitテーブルの`flag = 2` `type = 1 OR type = 3`の条件に合致するものを抽出したいです。
122
194
 
123
195
 
124
196
 
125
197
  **最終的に欲しい結果**
126
198
 
127
- facilities_trait.table
199
+ > facilities_trait.table
128
-
200
+
129
- |id|flag|type|rooms_id|facilities_id|
201
+ |id|flag|type|trait_id|facilities_id|
130
-
202
+
131
- |--:|--:|--:|--:|--:|
203
+ |--:|--:|--:|--:|--:|
132
-
204
+
133
- |1|2|1|1|12|
205
+ |1|2|1|101|12|
134
-
206
+
135
- |2|2|1|1|30|
207
+ |2|2|1|101|30|
136
-
137
- |3|2|1|1|**31**|
208
+
209
+
138
210
 
139
211
 
140
212
 
141
213
  ## 現状のコード
142
214
 
215
+ 分かりやすさを優先させましたが、`facilities_trait.trait_id`に入るarticlesとroomsのidは被る可能性があります(その為のtypeでもあります)。
216
+
217
+
218
+
143
219
  ```
144
220
 
145
221
  SELECT * FROM facilities_trait
146
222
 
147
- WHERE flag = 2 AND (type = 1 OR type = 3)
223
+ WHERE flag = 2 AND type IN (1, 3)
148
224
 
149
225
  AND EXISTS( SELECT 'X' FROM articles AS a WHERE r.articles_id = a.id AND a.flag = 2
150
226
 
@@ -154,7 +230,7 @@
154
230
 
155
231
  ```
156
232
 
157
- ひとまずは上記のコードで目的の結果は得られてると思います。
233
+ ひとまずは上記のコードで目的の結果は得られておりました。
158
234
 
159
235
  ただ、クエリがすごく重たいので`INNER JOIN`等で書き換えたいと思っております。
160
236
 

1

誤字修正しました。

2020/12/08 06:27

投稿

Dammyyy
Dammyyy

スコア7

test CHANGED
File without changes
test CHANGED
@@ -150,7 +150,7 @@
150
150
 
151
151
  AND EXISTS( SELECT 'X' FROM rooms AS r WHERE r.id = f.rooms_id AND r.flag = 2 AND r.status = 2
152
152
 
153
- AND EXISTS( SELECT 'X' FROM facilities_trait AS f WHERE r.id = f.rooms_id AND f.facilities_id = 91)))
153
+ AND EXISTS( SELECT 'X' FROM facilities_trait AS f WHERE r.id = f.rooms_id AND f.facilities_id = 31)))
154
154
 
155
155
  ```
156
156