How to use a JOIN inside a CASE expression?

Question

I have four tables like this:

company:

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

document_associated_company:

id	companyID	documentID
1	2	98
2	1	12

document

id	documentTypeID
98	67
12	87

document_type

id	name
67	NDA
87	SOW

And I have an SQL query like this:

SELECT CASE
  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:

status
active
customer

Answer 1

You can throw in an EXISTS expression:

SELECT CASE
        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.)