CODEWITHSUNDEEP
mysql
Ryan
Ryan

Reputation: 1164

SQL List data from table dependant on data from another table

I'm writing a CMS for a client who wishes for a sorting of his data by upcoming activities.

There is a table named customers which contains all of the customers, identified by the cust_id column.

There is also a table named activities, in which there is a cust_id column to pair it with a customer, and a date_last_done field to specify when the task was completed. The customer can have many activities to their record.

I want to fetch all customers where the most recent (date_last_done desc) activity was completed more than 11 months ago.

Here's my attempt:

SELECT
    *
FROM
    customers
INNER JOIN
    activities
ON
    activities.cust_id = customers.cust_id
WHERE
    (SELECT
        date_last_done
    FROM
        activities
    WHERE
        cust_id = ???
    ORDER BY
        date_last_done
    DESC)
    < date() - 11months

How would I edit my query to do this?

Upvotes: 0

Views: 15

Answers (1)

Barmar
Barmar

Reputation: 780879

Join the customers table with a subquery that returns the customers with activity that fits the criteria.

SELECT c.*
FROM customers AS c
JOIN (SELECT cust_id
      FROM activities
      GROUP BY cust_id
      HAVING MAX(date_last_done) < DATE_SUB(NOW(), INTERVAL 11 MONTH)) AS a
ON c.cust_id = a.cust_id

Upvotes: 1

Related Questions