SQL - select clients that haven't added any products in the last month


I am trying to write a query that selects all clients that haven’t added any products in the last month.
At the moment, I have this query that select all clients that haven’t added any products, but I need to ignore clients that have added products prior to the the month.

SELECT      tblClients.id, tblClients.name
FROM         tblClients
LEFT JOIN  tblProduct ON tblClients.id = tblProduct.client
WHERE      tblProduct.id IS NULL GROUP BY tblClients.id;

Gonna need more info than that. How do you define when a client added a product? does tblProduct include a date? What are the structures of these tables?

Yes, the tblProduct has a ‘insert_date’ field which is of type ‘date’
The relationship between tblClients and tblProduct is 1 to Many.

the trick to this type of query is to put the condition into the ON clause

oh, and you do not need GROUP BY here

SELECT tblClients.id
     , tblClients.name
  FROM tblClients
  JOIN tblProduct 
    ON tblProduct.client = tblClients.id 
   AND tblProduct.insert_date > CURRENT_DATE - INTERVAL 1 MONTH
 WHERE tblProduct.id IS NULL
1 Like

er… would this not result in duplication of names if a person has more than 1 product purchased in the previous month?

yeah, it would, if it weren’t for that WHERE condition!!


Thanks. This query did the trick

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.