Select records in one table and specific matching records from the other-CodePudding

I have three tables, two of which are relevant for this question. Users, Things, User_to_thing

Users

ID | Name  | Active
-------------------
1  | Joe   | 1
2  | Jack  | 1
3  | Tom   | 1
4  | Harry | 0
5  | Stan  | 1
6  | Bob   | 1

User_to_thing

Thing ID | User ID | Status
---------------------------
    3    |    1    |   1
    3    |    2    |   2
    3    |    5    |   1
    4    |    1    |   3
    4    |    2    |   2

I'm trying to create a query where I can select all the active users in the users table and have a column where I can see the status for "thing 3" from the User_to_thing table while also sorting results so that the nulls come at the end. So the result would be something like:

User ID | Status
----------------
   1    |   1
   2    |   2
   5    |   1
   3    |   NULL
   6    |   NULL

What I have so far for a query is the following:

SELECT u1.id, u1.name, user_to_thing.status
FROM users u1
LEFT JOIN user_to_thing ON u1.id = user_to_thing.user_id
WHERE u1.active = 1
OR user_to_thing.event_id = 62
ORDER BY (CASE WHEN user_to_thing.status = 1 THEN 1
WHEN user_to_thing.status = 2 THEN 2
ELSE 3 END)

What I'm getting as a result is the following:

User ID | Status | Thing ID
---------------------------
   1    |   1    |    3
   1    |   3    |    4
   2    |   2    |    3
   2    |   2    |    4
   5    |   1    |    3
   3    |  NULL  |   NULL
   6    |  NULL  |   NULL

I'm not sure how to limit it to just thing #3 while also getting a list of all active users. Any guidance would be appreciated.

CodePudding user response：

It looks like the following should work for you, grouping to remove duplicates and ordering based on null

select u.Id as UserId, t.status 
from users u
left join User_to_thing t on t.UserID = u.id
where u.active = 1
group by u.Id, t.Status
order by case when status is null then 1 else 0 end, u.Id

Based on your revised data, you can amend slightly

select u.Id UserId, Min(t.status) Status
from users u
left join User_to_thing t on t.UserID=u.id
where u.active=1
group by u.Id
order by case when Min(t.status) is null then 1 else 0 end, u.Id