I need to add a where clause to this select from with a left join but getting syntax error-CodePudding

Ok so I have this SQL statement which returns almost what I want

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

The problem is that I need to get only results where u.study_id and u.site_id are inside some values, thus I figured I would add the following

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

right after the from users u

thus

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

however this is throwing a syntax error

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

So I would like to be able to make my Where clause work to only select users where study_id = study, and site_id = site

If it helps I am using Postgres. ^

CodePudding user response：

Where clause after join

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