Multiple joined tables and conditions-CodePudding

I have the following tables structure:

users
 - id int(PK)
 - role varchar(20)

questions
 - id int (PK)
 - status varchar(20)

answers
 - id int (PK)
 - question_id int (refs questions id)
 - user_id int (refs users id)
 - created_at timestamp

My goal would be to get those questions where the status is 'opened', the last (based on created at) answer is made by a user whose role is admin and the last answer made by a user (non-admin) was at least 1 week ago.

I came up with a veeery long query, most of it works, but the problem is that if there is an answer by a user that is not the last but was at least 1 week ago, it triggers as well.

I'm also opened to simplify my query to remove that bunch of inner joins...

select distinct q.* from questions q 
inner join answers a on a.question_id = q.id
inner join answers a2 on a.question_id = q.id
inner join users u ON u.id = a.user_id
WHERE q.status = 'opened' AND u.role = 'admin' 
and a.id in (select a3.id from answers a3 inner join questions q2 on q.id = a3.ticket_id inner join users u on u.id = a3.user_id where u."role" = 'admin' and a3.created_at = (select max(a3.created_at) from "answers" a3 where a3.question_id = q2.id))
and a2.id in (select a.id from "answers" a
inner join users u on u.id = a.user_id 
where u."role" = 'user'
and a.created_at < (SELECT now() - interval '1 week'))

CodePudding user response：

You are joining the answers table twice without filtering it. You can solve this by using subqueries, something like this:

SELECT questions.*
FROM questions
JOIN (
  SELECT question_id, MAX(created_at) AS max_created_at
  FROM answers
  WHERE user_id IN (SELECT id FROM users WHERE role = 'admin')
  GROUP BY question_id
) admin_answers ON questions.id = admin_answers.question_id
JOIN (
  SELECT question_id, MAX(created_at) AS max_created_at
  FROM answers
  WHERE user_id NOT IN (SELECT id FROM users WHERE role = 'admin')
  GROUP BY question_id
) nonadmin_answers ON questions.id = nonadmin_answers.question_id
WHERE questions.status = 'opened'
AND admin_answers.max_created_at = (SELECT MAX(created_at) FROM answers WHERE question_id = questions.id)
AND nonadmin_answers.max_created_at <= NOW() - INTERVAL '1 week'

If not working give me some sample data for the tables and the expected result and will check it