I have four tables like this:


id companyContactID customerID companyTeamID
1 12 21 54
2 14 12 78


id companyID documentID
1 2 98
2 1 12


id documentTypeID
98 67
12 87


id name
67 NDA
87 SOW

And I have an SQL query like this:

  WHEN company."customerID" IS NOT NULL THEN 'customer'
  WHEN company."companyContactID" IS NOT NULL THEN 'lead'
  WHEN company."companyTeamID" IS NOT NULL THEN 'lead'
  ELSE 'company'
  END AS status
FROM   company; 

I have used a CASE expression here to create a new column named status. Basically, according to the several conditions I set the status of the company. Additionally, I need to check whether the company has a document attached with a certain type. For example, if the company have a document with the type NDA attached, then its status would be 'active'. For this I would have to put several JOIN statements. Any idea how to integrate this into the CASE expression?

Expected result:

  • Where 'active' is taken from? The status for both rows in company is 'customer' and not 'company', so additional rule shouldn't be applied. PS. dbfiddle.uk/…
    – Akina
    Jul 29, 2022 at 7:49
  • @ErgestBasha No it's not. For both those conditions, "lead" should be the result. I should be refactoring it by adding and AND.
    – THpubs
    Jul 29, 2022 at 8:23

1 Answer 1


You can throw in an EXISTS expression:

        WHEN EXISTS (SELECT  -- select list can be empty
                     FROM    document_associated_company a
                     JOIN    document                    d ON d.id = a."documentID"
                     JOIN    document_type               t ON t.id = d."documentTypeID"
                     WHERE   a."comanyID" = c.id
                     AND     t.name = 'NDA') THEN 'active'
        WHEN c."customerID"       IS NOT NULL THEN 'customer'
        WHEN c."companyContactID" IS NOT NULL THEN 'lead'
        WHEN c."companyTeamID"    IS NOT NULL THEN 'lead'
        ELSE 'company'
       END AS status
FROM   company c;

(Making a couple of assumptions about your relational design.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.