Mysql – How to group records under certain conditons in thesql

MySQL

I would like to join 2 tables and group user records based on user id and return its overall status.

Example. Lets say a user applied for a loan, he uploaded multiple documents to bank website which are required to approved by the bank.

if all docs are approved by bank then the status must verified ,

If at least one document is "verified", the overall status is "verified".

If no document is "verified" but at least one is "in progress", the overall status is "in progress".

If no document is "verified" and "in progress", but at least one is "expired", the overall status is "expired".

If no document is "verified", "in progress" and "expired", but at least one is "rejected", the overall status is "rejected".

If no document is uploaded at all, the status is "no xyz uploaded".
(for documents without expiration date the status is "expired")

Two tables : customer and customerDocs

user has id, name, phone email

customerDoc has id, customerID(reference key), verificationStatus, description, expDate

SELECT 
    cu.id CustomerID,
    CASE WHEN cd.verification_status = 'PAID'
     THEN 'PAID'
     WHEN cd.verification_status = 'PENDING'
     THEN 'PENDING'
     WHEN cd.verification_status <> 'PAID' && cd.verification_status = 'PENDING'
     THEN 'PENDING'
     WHEN cd.verification_status <> 'PAID' && cd.verification_status <> 'PENDING'
          && cd.verification_status = 'CLOSED'
     THEN 'CLOSED'
     WHEN cd.verification_status <> 'PAID' && cd.verification_status <> 'PENDING'
          && cd.verification_status <> 'CLOSED'
     THEN 'REJECTED'
     WHEN cd.expire_date IS NULL
     THEN 'CLOSED'
     ELSE 'No status'  END STATUS
FROM customer cu 
    JOIN customer_details cd ON cu.id = cd.customer
GROUP BY cu.id

I tried the above query but not getting expected output. So any help here

Best Answer

Schematically:

SELECT user.id
       CASE 
       -- if all docs are approved by bank then the status must 'verified'
            WHEN SUM(document.status = 'approved') = COUNT(document.id)
                THEN 'verified'
       -- If at least one document is 'verified', the overall status is 'verified'
            WHEN SUM(document.status = 'verified')
                THEN 'verified'
       -- If no document is 'verified' but at least one is 'in progress', the overall status is 'in progress'.
            WHEN SUM(document.status = 'in progress')
                THEN 'in progress'
       -- If no document is 'verified' and 'in progress', but at least one is 'expired', the overall status is 'expired'.
            WHEN SUM(document.status = 'expired')
                THEN 'expired'
       -- If no document is 'verified', 'in progress' and 'expired', but at least one is 'rejected', the overall status is 'rejected'.
            WHEN SUM(document.status = 'rejected')
                THEN 'rejected'
       -- If no document is uploaded at all, the status is 'no xyz uploaded' 
                ELSE 'no docs uploaded'
       END AS `overall_status`
FROM user 
JOIN document ON (joining conditions)
GROUP BY user.id

(for documents without expiration date the status is 'expired')

I don't understand the place of this condition.

Related Question