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