CODEWITHSUNDEEP
mysqlsqljoincasewhere-clause
mikefolu
mikefolu

Reputation: 1323

How to combine MYSQL Left Join and Group By for another Table

I am using mysql and have two tables user and result.

      `user`                                 `result`
---------------------------          -------------------------------------
| `id`  | `name` |`active`|          | `id` |`user_id` |`status`|`identity`|
--------------------------            ------------------------------------
|   1   | Apia   | 1    |            |   1   |    1   |    2   |    1   |
|   2   | Tang   | 1    |            |   2   |    1   |    2   |    1   |
|   3   | Jemrom | 1    |            |   3   |    2   |    1   |    1   |
|   4   | Akwet  | 1    |            |   4   |    5   |    3   |    1   |
|   5   | Lamte  | 1    |            -------------------------------------
---------------------------

So the result should look like:

------------------------------
| `name` |`active` |`status`  |
------------------------------
| Apia   |    1    |`passed`  |
| Tang   |    1    |`passed`  |
| Akwet  |    1    |`awaiting`|
| Lamte  |    1    |`failed`  |
| Jerome  |    1    |`unavailable`  |
------------------------------

So far, I have:

SELECT     u.*, 
           r.status
FROM       user u 
LEFT JOIN result r 
ON         r.user_id = u.id 
WHERE u.active = 1;

But I don't know how to write the query for result table aspect.

NB: For status in result table (1=awaiting, 2=passed,3=failed)

I want to select everything from user table using left join where active = 1 (ordered by names) and match with related user_id in result table where identity =1. The result table must be grouped by user_id. Where a user does not have data in result, it should display unavailable for status instead of null.

How do I write this query in MYSQL?

Thanks

Upvotes: 1

Views: 2832

Answers (1)

GMB
GMB

Reputation: 222432

You seem to want a left join and aggregation. Your question is unclear about how you want to compute the status when a user has more than one result, so I assumed you want the maximum value:

select
    u.name,
    u.active,
    case max(r.status)
        when 1 then 'awaiting'
        when 2 then 'passed'
        when 3 then 'failed'
        else 'unavailable'
    end as status
from users u
left join result r on r.user_id = u.id and r.identity = 1
where u.active = 1
group by u.id, u.name, u.active

Note that user is a language keyword in MySQL (as well as in most other databases), hence a poor choice for a table name. I renamed it to users inthe query.

Upvotes: 1

Related Questions