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

編集履歴

質問編集履歴

3

書式の改善

2020/12/08 06:45

投稿

Dammyyy
Dammyyy

スコア7

title CHANGED
File without changes
body CHANGED
@@ -108,11 +108,10 @@
108
108
  分かりやすさを優先させましたが、`facilities_trait.trait_id`に入るarticlesとroomsのidは被る可能性があります(その為のtypeでもあります)。
109
109
 
110
110
  ```
111
- SELECT * FROM facilities_trait
111
+ SELECT * FROM facilities_trait WHERE flag = 2 AND (type = 1 OR type = 3)
112
- WHERE flag = 2 AND type IN (1, 3)
113
- AND EXISTS( SELECT 'X' FROM articles AS a WHERE r.articles_id = a.id AND a.flag = 2
112
+ AND EXISTS( SELECT 'X' FROM articles WHERE flag = 2 AND facilities_trait.trait_id = articles.id
114
- AND EXISTS( SELECT 'X' FROM rooms AS r WHERE r.id = f.rooms_id AND r.flag = 2 AND r.status = 2
113
+ AND EXISTS( SELECT 'X' FROM rooms WHERE flag = 2 AND status = 2 AND rooms.articles_id = articles.id
115
- AND EXISTS( SELECT 'X' FROM facilities_trait AS f WHERE r.id = f.rooms_id AND f.facilities_id = 31)))
114
+ AND EXISTS( SELECT 'X' FROM facilities_trait WHERE rooms.id = facilities_trait.trait_id AND facilities_trait.facilities_id = 31)))
116
115
  ```
117
116
  ひとまずは上記のコードで目的の結果は得られておりました。
118
117
  ただ、クエリがすごく重たいので`INNER JOIN`等で書き換えたいと思っております。

2

質問内容の明確化

2020/12/08 06:45

投稿

Dammyyy
Dammyyy

スコア7

title CHANGED
File without changes
body CHANGED
@@ -4,79 +4,117 @@
4
4
  articles.table
5
5
  |id|flag|title|
6
6
  |--:|--:|--:|
7
- |1|2|××アパート|
7
+ |101|2|××アパート|
8
- |2|1|〇〇マンション|
8
+ |102|1|〇〇マンション|
9
- |3|2|△マンション|
9
+ |103|2|△マンション|
10
+ |:|:|:|
10
11
 
11
12
  rooms.table
12
13
  |id|flag|status|articles_id|
13
14
  |--:|--:|--:|--:|
14
- |1|2|2|1|
15
+ |1|2|2|101|
15
- |2|1|2|1|
16
+ |2|1|2|101|
16
- |3|2|2|2|
17
+ |3|2|2|102|
17
- |4|1|2|3|
18
+ |4|1|2|103|
18
- |5|2|1|3|
19
+ |5|2|1|103|
19
- |6|2|1|3|
20
+ |6|2|1|103|
21
+ |:|:|:|:|
20
22
 
21
23
  facilities_trait.table
22
- |id|flag|type|rooms_id|facilities_id|
24
+ |id|flag|type|trait_id|facilities_id|
23
25
  |--:|--:|--:|--:|--:|
24
- |1|2|1|1|12|
26
+ |1|2|1|101|12|
25
- |2|2|1|1|30|
27
+ |2|2|1|101|30|
26
- |3|2|1|1|31|
28
+ |3|2|2|1|31|
27
- |4|2|1|2|12|
29
+ |4|2|1|102|12|
28
- |5|2|1|2|30|
30
+ |5|2|1|102|30|
29
- |6|2|2|3|12|
31
+ |6|2|1|103|12|
30
32
  |7|2|2|3|28|
31
- |8|2|2|3|30|
33
+ |8|2|1|103|30|
32
34
  |9|2|2|3|31|
33
- |10|2|1|4|30|
35
+ |10|2|1|104|30|
34
- |11|2|1|4|31|
36
+ |11|2|2|4|31|
35
- |12|2|3|5|25|
37
+ |12|2|3|105|25|
36
- |13|2|3|5|30|
38
+ |13|2|1|105|30|
37
- |14|2|3|5|31|
39
+ |14|2|2|5|31|
38
40
  |15|2|2|6|27|
39
- |16|2|2|6|30|
41
+ |16|2|1|106|30|
40
42
 
43
+ facilities_traitテーブルのtrait_idには`articles.id`と`rooms.id`が混在していて、typeでarticlesかroomsか判別しています。
44
+
41
45
  ## 実装したいこと
42
- この時、facilities_traitテーブルの`facilities_id = 31` を持つ rooms_id を全て取り出します
43
46
 
44
- **絞り込み後**
47
+ **1.**
48
+ まず、facilities_traitテーブルの`facilities_id = 31` を持つ trait_id を全て取り出します。
45
- facilities_trait.table
49
+ > facilities_trait.table
46
- |id|flag|type|rooms_id|facilities_id|
50
+ |id|flag|type|trait_id|facilities_id|
47
51
  |--:|--:|--:|--:|--:|
48
- |1|2|1|1|12|
49
- |2|2|1|1|30|
52
+ |3|2|2|1|31|
50
- |3|2|1|1|**31**|
51
- |6|2|2|3|12|
52
- |7|2|2|3|28|
53
- |8|2|2|3|30|
53
+ |9|2|2|3|31|
54
- |9|2|2|3|**31**|
55
- |10|2|1|4|30|
54
+ |11|2|2|4|31|
56
- |11|2|1|4|**31**|
57
- |12|2|3|5|25|
58
- |13|2|3|5|30|
55
+ |14|2|2|5|31|
59
- |14|2|3|5|**31**|
60
56
 
57
+ **2.**
61
- その上で、articlesテーブルの`flag = 2`と、roomsテーブルの`flag = 2` `status = 2`、facilities_traitテーブルの`flag = 2` `type = 1 OR type = 3`の条件に合致するものを抽出したいです。
58
+ `type = 2`なのでroomsテーブルを見て、`facilities.trait_id = rooms.id`で紐づくものを取り出します。
59
+ > rooms.table
60
+ |id|flag|status|articles_id|
61
+ |--:|--:|--:|--:|
62
+ |1|2|2|101|
63
+ |3|2|2|102|
64
+ |4|1|2|103|
65
+ |5|2|1|103|
62
66
 
67
+ その際`rooms.flag = 2`,`rooms.status = 2`以外のものは除外します。
68
+ > rooms.table
69
+ |id|flag|status|articles_id|
70
+ |--:|--:|--:|--:|
71
+ |1|2|2|101|
72
+ |3|2|2|102|
73
+
74
+ **3.**
75
+ articlesテーブルで`rooms.articles_id`に紐づくものを取り出します。
76
+
77
+ > articles.table
78
+ |id|flag|title|
79
+ |--:|--:|--:|
80
+ |101|2|××アパート|
81
+ |102|1|〇〇マンション|
82
+
83
+ その際`articles.flag = 2`以外のものは除外します。
84
+ > articles.table
85
+ |id|flag|title|
86
+ |--:|--:|--:|
87
+ |101|2|××アパート|
88
+
89
+ **4.**
90
+ `articles.id`をもって改めてfacilitiesテーブルに戻り、`articles.id = facilities_trait.trait_id`を取り出します。
91
+ > facilities_trait.table
92
+ |id|flag|type|trait_id|facilities_id|
93
+ |--:|--:|--:|--:|--:|
94
+ |1|2|1|101|12|
95
+ |2|2|1|101|30|
96
+
97
+ ここで、facilities_traitテーブルの`flag = 2` `type = 1 OR type = 3`の条件に合致するものを抽出したいです。
98
+
63
99
  **最終的に欲しい結果**
64
- facilities_trait.table
100
+ > facilities_trait.table
65
- |id|flag|type|rooms_id|facilities_id|
101
+ |id|flag|type|trait_id|facilities_id|
66
102
  |--:|--:|--:|--:|--:|
67
- |1|2|1|1|12|
103
+ |1|2|1|101|12|
68
- |2|2|1|1|30|
104
+ |2|2|1|101|30|
69
- |3|2|1|1|**31**|
70
105
 
106
+
71
107
  ## 現状のコード
108
+ 分かりやすさを優先させましたが、`facilities_trait.trait_id`に入るarticlesとroomsのidは被る可能性があります(その為のtypeでもあります)。
109
+
72
110
  ```
73
111
  SELECT * FROM facilities_trait
74
- WHERE flag = 2 AND (type = 1 OR type = 3)
112
+ WHERE flag = 2 AND type IN (1, 3)
75
113
  AND EXISTS( SELECT 'X' FROM articles AS a WHERE r.articles_id = a.id AND a.flag = 2
76
114
  AND EXISTS( SELECT 'X' FROM rooms AS r WHERE r.id = f.rooms_id AND r.flag = 2 AND r.status = 2
77
115
  AND EXISTS( SELECT 'X' FROM facilities_trait AS f WHERE r.id = f.rooms_id AND f.facilities_id = 31)))
78
116
  ```
79
- ひとまずは上記のコードで目的の結果は得られてると思います。
117
+ ひとまずは上記のコードで目的の結果は得られておりました。
80
118
  ただ、クエリがすごく重たいので`INNER JOIN`等で書き換えたいと思っております。
81
119
 
82
120
  皆様の知恵をお借りしたいです。

1

誤字修正しました。

2020/12/08 06:27

投稿

Dammyyy
Dammyyy

スコア7

title CHANGED
File without changes
body CHANGED
@@ -74,7 +74,7 @@
74
74
  WHERE flag = 2 AND (type = 1 OR type = 3)
75
75
  AND EXISTS( SELECT 'X' FROM articles AS a WHERE r.articles_id = a.id AND a.flag = 2
76
76
  AND EXISTS( SELECT 'X' FROM rooms AS r WHERE r.id = f.rooms_id AND r.flag = 2 AND r.status = 2
77
- AND EXISTS( SELECT 'X' FROM facilities_trait AS f WHERE r.id = f.rooms_id AND f.facilities_id = 91)))
77
+ AND EXISTS( SELECT 'X' FROM facilities_trait AS f WHERE r.id = f.rooms_id AND f.facilities_id = 31)))
78
78
  ```
79
79
  ひとまずは上記のコードで目的の結果は得られてると思います。
80
80
  ただ、クエリがすごく重たいので`INNER JOIN`等で書き換えたいと思っております。