postgresql

I use subquery but the subquery works only on one user

I have a employee list. I left join users_ill to check if a user is ill or not. The problem is now, I got two users that are ill but only one is showed as ill. If I change the order then the different user is ill. Everytime only one is ill not more. What I am doing wrong ? in my database there are more then one ill (or more then one has health_at NULL)

Users Ill Schema

id | int
user_id | uuid
health_at | timestamp null
created_at | timestamp

Code:

SELECT 
 u.id as user_id, u.firstname, u.lastname, u.created_at,
 ui.created_at as ill_since
 FROM users u

 LEFT JOIN (
   SELECT user_id, created_at FROM users_ill WHERE health_at IS NULL ORDER BY created_at DESC 
   LIMIT 1
 ) ui ON (ui.user_id = u.id)

WHERE u.tenant_id = $1 AND u.deleted_by IS NULL
ORDER BY u.firstname ASC LIMIT 50 OFFSET $8;
Solution

  • Make really a subselect, for every user the latest date of an open illness, like

    SELECT 
 u.id as user_id, u.firstname, u.lastname, u.created_at,
 (select max(ui.created_at) FROM users_ill ui WHERE health_at IS NULL
  and u.id = ui.user_id) as ill_since
 FROM users u
WHERE u.tenant_id = $1 AND u.deleted_by IS NULL
ORDER BY u.firstname ASC LIMIT 50 OFFSET $8;