我需要向这个select from添加一个where子句，该子句带有左联接，但出现语法错误

Question

好的，所以我有了这个SQL语句，它几乎返回了我想要的

SELECT
u.id,
u.study_id,
u.site_id,
a.systems_time AS first_contact_time,
a.type AS first_contact_type
FROM
users u
LEFT JOIN (
SELECT
s.user_id,
s.type,
min(s.timestamp) AS systems_time
FROM
systems s
WHERE
TYPE in('S1', 'S2', 'S3', 'S4', 'S5', 'S6')
GROUP BY
s.user_id,
s.type) s ON s.user_id = u.id
LEFT JOIN (
SELECT
a.user_id,
a.type,
min(a.created_at) AS status_time
FROM
activities a
WHERE
TYPE in('EMAIL', 'SMS')
GROUP BY
a.user_id,
a.type) a ON a.user_id = u.id
ORDER BY
1

问题是，我只需要得到u.study_id和u.site_id在某些值内的结果，因此我想我应该添加以下

where
u.study_id in ('study') and
u.site_id in ('site')

就在from users u之后

因此

SELECT
u.id,
u.study_id,
u.site_id,
a.systems_time AS first_contact_time,
a.type AS first_contact_type
FROM
users u
where
u.study_id in ('study') and
u.site_id in ('site')
LEFT JOIN (
SELECT
s.user_id,
s.type,
min(s.timestamp) AS systems_time
FROM
systems s
WHERE
TYPE in('S1', 'S2', 'S3', 'S4', 'S5', 'S6')
GROUP BY
s.user_id,
s.type) s ON s.user_id = u.id
LEFT JOIN (
SELECT
a.user_id,
a.type,
min(a.created_at) AS status_time
FROM
activities a
WHERE
TYPE in('EMAIL', 'SMS')
GROUP BY
a.user_id,
a.type) a ON a.user_id = u.id
ORDER BY
1

然而，这抛出了一个语法错误

Query 1 ERROR: ERROR:  syntax error at or near "where"
LINE 10: where

因此，我希望能够使Where子句只选择study_id=study和site_id=site的用户

如果有帮助的话，我正在使用Postgres。^

Answer 1

加入后的Where子句

SELECT
u.id,
u.study_id,
u.site_id,
a.systems_time AS first_contact_time,
a.type AS first_contact_type
FROM
users u
LEFT JOIN (
SELECT
s.user_id,
s.type,
min(s.timestamp) AS systems_time
FROM
systems s
WHERE
TYPE in('S1', 'S2', 'S3', 'S4', 'S5', 'S6')
GROUP BY
s.user_id,
s.type) s ON s.user_id = u.id
LEFT JOIN (
SELECT
a.user_id,
a.type,
min(a.created_at) AS status_time
FROM
activities a
WHERE
TYPE in('EMAIL', 'SMS')
GROUP BY
a.user_id,
a.type) a ON a.user_id = u.id
where
u.study_id in ('study') and
u.site_id in ('site')
ORDER BY