Calculate the value in a column based on JOIN results?

I have a table, Account, which I am querying to identify old accounts based on sales records. An account may have multiple rows in a Property table. A property may be closed. If all properties on an account are closed, the account should be returned as it is old.

How do I identify an account where all properties are closed?

Additionally, an account is considered old if they have not purchased a Service in 24 months.

As an example, given the following tables:

-- ACCOUNT --
+-------+-----------------------------------+
| ac_id | ac_email                          |
+-------+-----------------------------------+
|  1416 | bob@bob.com                       |
|  1419 | joe@joe.com                       |
+-------+-----------------------------------+

-- PROPERTY --

+------+---------------+-------+
| p_id | p_closed      | ac_id |
+------+---------------+-------+
|    3 |  FALSE        |  1416 |
|    6 |  TRUE         |  1419 |
|    7 |  TRUE         |  1419 |
+------+---------------+-------+

--SERVICE--

+------+------------+
| p_id | s_saledate |
+------+------------+
|    3 | 2010-03-17 |
|    3 | 2011-02-16 |
|    6 | 2022-11-14 |
|    7 | 2022-01-24 |
+------+------------+

I would expect the following returned table

+------------+-----------------------+-----------------------+--------------+------------+--------------+------------------+
| account_id | email                 | all_properties_closed | property_ids | latest_sale_date |
+------------+-----------------------+-----------------------+--------------+------------+--------------+------------------+
|       1416 | bob@bob.com           |       FALSE           |     9            | 2011-02-16       |
|       1419 | joe@joe.com           |       TRUE            |    6,7           | 2022-11-14       |
+------------+-----------------------+----------------+---------------------------+--------------+------------------+

Bob's account is returned because he's not had a Service in over 2 years & Joe's account is returned because he has had a service in 2 years, but all of his properties are closed.

This is my current query

 SELECT
 ac_id as account_id,
 GROUP_CONCAT(DISTINCT p_id) as property_ids,
 MAX(sale_date) as latest_sale_date
 FROM Account
 JOIN Property USING (ac_id)
 JOIN Service USING (p_id)
 GROUP BY ac.ac_id
 HAVING latest_sale_date < 2021-03-31

I imagine I need to use the ALL operator and WHERE to get the all_properties_closed column, but I'm unsure how. Do I need a subquery?

You can compute all_properties_closed with conditional aggregation. Assuming that p_closed is of a string-like datatype, that would look like:

SELECT
    ac_id as account_id,
    MIN(p_closed = 'FALSE') all_properties_closed
    GROUP_CONCAT(DISTINCT p_id) as property_ids,
    MAX(sale_date) as latest_sale_date
FROM Account
JOIN Property USING (ac_id)
JOIN Service USING (p_id)
GROUP BY ac.ac_id
HAVING latest_sale_date < 2021-03-31 OR all_properties_closed

Expression MIN(p_closed = 'FALSE') yields 0 if any p_closed in the group has a non-'FALSE' value, else it returns 1; we can use this information to filter in the HAVING clause.

In normal usage you would have a 0/1 flag in column p_closed, and we could just do:

MIN(p_closed) all_properties_closed

Yes, you can use a subquery to check if all properties for an account are closed. Here's a modified version of your query that adds the all_properties_closed column:

SELECT
 ac.ac_id as account_id,
 ac.ac_email as email,
 GROUP_CONCAT(DISTINCT p.p_id) as property_ids,
 MAX(s.s_saledate) as latest_sale_date,
 NOT EXISTS (
   SELECT 1 FROM Property p2 WHERE p2.ac_id = ac.ac_id AND p2.p_closed = FALSE
 ) as all_properties_closed
FROM Account ac
LEFT JOIN Property p ON p.ac_id = ac.ac_id
LEFT JOIN Service s ON s.p_id = p.p_id
GROUP BY ac.ac_id
HAVING latest_sale_date < 2021-03-31 OR all_properties_closed

In the subquery, we're checking if there exists a property for the same account that is not closed (p2.p_closed = FALSE). If there is no such property, then NOT EXISTS returns TRUE, indicating that all properties are closed.

Note that I've also added ac.ac_email to the select list and used LEFT JOIN instead of JOIN to ensure that we get all accounts, even if they don't have any properties or services.

The HAVING clause at the end includes both criteria for identifying old accounts: those with no services after the given date, or those with all properties closed.