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

Databases
1

Hello

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;
2

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?

3

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

4

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
LEFT OUTER
  JOIN tblProduct 
    ON tblProduct.client = tblClients.id 
   AND tblProduct.insert_date > CURRENT_DATE - INTERVAL 1 MONTH
 WHERE tblProduct.id IS NULL
1 Like
5

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

6

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

2 Likes
7

Thanks. This query did the trick

8

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