Select distinct records if status is not null-CodePudding

I have a leave application table which has duplicate records.

Leave application table:

leave_app_id  user_id    from_date      to_date      reason       status  approved_by
    1           5        2021-08-07     2021-08-09   Sick Leave   NULL    NULL
    2           5        2021-08-07     2021-08-09   Sick Leave   NULL    NULL
    3           5        2021-08-07     2021-08-09   Sick Leave   1       9
    4           10       2021-09-02     2021-09-05   Medical      NULL    NULL
    5           10       2021-09-02     2021-09-05   Medical      NULL    NULL

Got distinct records by using this query:

SELECT
    MIN(leave_app_id) leave_app_id,
    a.user_id,
    a.from_date,
    a.to_date,
    a.reason,
    a.status,
    (SELECT CONCAT_WS(' ', first_name, last_name) FROM tbl_users WHERE user_id = a.approved_by) approved_by
FROM
    tbl_leave_applications a
    INNER JOIN tbl_users u ON u.user_id = a.user_id
GROUP BY
    a.user_id,
    a.from_date,
    a.to_date,
    a.reason,
    a.status,
    a.approved_by

Result of above query:

leave_app_id  user_id    from_date      to_date      reason       status  approved_by
    1           5        2021-08-07     2021-08-09   Sick Leave   NULL    NULL
    3           5        2021-08-07     2021-08-09   Sick Leave   1       John Doe
    4           10       2021-09-02     2021-09-05   Medical      NULL    NULL

However I want, if status is not null then it should display that record only.

Like this:

leave_app_id  user_id    from_date      to_date      reason       status  approved_by
    3           5        2021-08-07     2021-08-09   Sick Leave   1       John Doe
    4           10       2021-09-02     2021-09-05   Medical      NULL    NULL

CodePudding user response：

You could try aggregation here:

SELECT
    COALESCE(MAX(CASE WHEN a.status IS NOT NULL THEN a.leave_app_id END), MIN(a.leave_app_id)) AS leave_app_id,
    a.user_id,
    a.from_date,
    a.to_date,
    a.reason,
    MAX(a.status) AS status,
    MAX(CONCAT(u.first_name, ' ', u.last_name)) AS approved_by
FROL tbl_leave_applications a
INNER JOIN tbl_users u ON u.user_id = a.user_id
GROUP BY
    a.user_id,
    a.from_date,
    a.to_date,
    a.reason;