回答編集履歴
4
再び3つ目がダメ
test
CHANGED
|
@@ -136,12 +136,28 @@
|
|
|
136
136
|
|
|
137
137
|
ON c.chat_group_id = cg.id
|
|
138
138
|
|
|
139
|
+
LEFT JOIN (
|
|
140
|
+
|
|
141
|
+
SELECT
|
|
142
|
+
|
|
143
|
+
cr.*
|
|
144
|
+
|
|
145
|
+
FROM
|
|
146
|
+
|
|
139
|
-
|
|
147
|
+
chat_read as cr
|
|
148
|
+
|
|
149
|
+
INNER JOIN staff as s
|
|
150
|
+
|
|
151
|
+
ON cr.staff_id = s.id
|
|
152
|
+
|
|
153
|
+
WHERE
|
|
154
|
+
|
|
155
|
+
s.name = 'foo_staff'
|
|
156
|
+
|
|
157
|
+
) as cr
|
|
140
158
|
|
|
141
159
|
ON c.id = cr.chat_id
|
|
142
160
|
|
|
143
|
-
AND cr.staff_id IN(SELECT id FROM staff WHERE name = 'foo_staff')
|
|
144
|
-
|
|
145
161
|
WHERE
|
|
146
162
|
|
|
147
163
|
cr.id IS NOT NULL -- chat_readのid列がNULLか否かでデータ有無を判断できる。
|
3
3つ目のサンプルが誤ってそう
test
CHANGED
|
@@ -110,7 +110,7 @@
|
|
|
110
110
|
|
|
111
111
|
#あるstaffの名前だけが事前に分かる場合
|
|
112
112
|
|
|
113
|
-
この場合はstaffテーブルも
|
|
113
|
+
この場合はstaffテーブルも検索対象に含める必要があります。
|
|
114
114
|
|
|
115
115
|
|
|
116
116
|
|
|
@@ -140,11 +140,7 @@
|
|
|
140
140
|
|
|
141
141
|
ON c.id = cr.chat_id
|
|
142
142
|
|
|
143
|
-
LEFT JOIN staff as s
|
|
144
|
-
|
|
145
|
-
ON cr.staff_id = s.id
|
|
146
|
-
|
|
147
|
-
AND s
|
|
143
|
+
AND cr.staff_id IN(SELECT id FROM staff WHERE name = 'foo_staff')
|
|
148
144
|
|
|
149
145
|
WHERE
|
|
150
146
|
|
2
表記ミス修正
test
CHANGED
|
@@ -14,7 +14,7 @@
|
|
|
14
14
|
|
|
15
15
|
|
|
16
16
|
|
|
17
|
-
以下はfoo_staff(staff_id)の**既読メッセージを取得するクエリ**です。
|
|
17
|
+
以下はfoo_staff(staff_id = 2)の**既読メッセージを取得するクエリ**です。
|
|
18
18
|
|
|
19
19
|
(※今回の質問の参考になるよう一部非効率なやり方をしてます)
|
|
20
20
|
|
1
参考ソース追記とミス修正
test
CHANGED
|
@@ -38,7 +38,7 @@
|
|
|
38
38
|
|
|
39
39
|
ON c.chat_group_id = cg.id
|
|
40
40
|
|
|
41
|
-
LEFT JOIN chat_read cr
|
|
41
|
+
LEFT JOIN chat_read as cr
|
|
42
42
|
|
|
43
43
|
ON c.id = cr.chat_id
|
|
44
44
|
|
|
@@ -136,16 +136,72 @@
|
|
|
136
136
|
|
|
137
137
|
ON c.chat_group_id = cg.id
|
|
138
138
|
|
|
139
|
-
LEFT JOIN chat_read cr
|
|
139
|
+
LEFT JOIN chat_read as cr
|
|
140
140
|
|
|
141
141
|
ON c.id = cr.chat_id
|
|
142
142
|
|
|
143
|
-
LEFT JOIN staff s
|
|
143
|
+
LEFT JOIN staff as s
|
|
144
144
|
|
|
145
145
|
ON cr.staff_id = s.id
|
|
146
146
|
|
|
147
147
|
AND s.name = 'foo_staff'
|
|
148
148
|
|
|
149
|
+
WHERE
|
|
150
|
+
|
|
151
|
+
cr.id IS NOT NULL -- chat_readのid列がNULLか否かでデータ有無を判断できる。
|
|
152
|
+
|
|
153
|
+
```
|
|
154
|
+
|
|
155
|
+
|
|
156
|
+
|
|
157
|
+
EXISTS版を投下し忘れてたので投下。
|
|
158
|
+
|
|
159
|
+
```SQL
|
|
160
|
+
|
|
161
|
+
SELECT
|
|
162
|
+
|
|
163
|
+
c.*
|
|
164
|
+
|
|
165
|
+
, cg.*
|
|
166
|
+
|
|
167
|
+
FROM
|
|
168
|
+
|
|
169
|
+
chat as c
|
|
170
|
+
|
|
171
|
+
INNER JOIN chat_group as cg
|
|
172
|
+
|
|
173
|
+
ON c.chat_group_id = cg.id
|
|
174
|
+
|
|
175
|
+
WHERE
|
|
176
|
+
|
|
177
|
+
-- 下記は該当レコードある場合に条件成立
|
|
178
|
+
|
|
179
|
+
-- レコードがない場合に条件成立する構文に「NOT EXISTS」もある
|
|
180
|
+
|
|
181
|
+
EXISTS(
|
|
182
|
+
|
|
183
|
+
-- foo_staffの既読情報を取得するクエリ
|
|
184
|
+
|
|
185
|
+
SELECT
|
|
186
|
+
|
|
187
|
+
*
|
|
188
|
+
|
|
189
|
+
FROM
|
|
190
|
+
|
|
191
|
+
chat_read as cr
|
|
192
|
+
|
|
193
|
+
INNER JOIN staff as s
|
|
194
|
+
|
|
195
|
+
ON cr.staff_id = s.id
|
|
196
|
+
|
|
197
|
+
WHERE
|
|
198
|
+
|
|
199
|
+
cr.chat_id = c.id
|
|
200
|
+
|
|
201
|
+
AND s.name = 'foo_staff'
|
|
202
|
+
|
|
203
|
+
)
|
|
204
|
+
|
|
149
205
|
```
|
|
150
206
|
|
|
151
207
|
|