I need help with a MySQL query. I have 2 tables, clients and payments. The table clients has 2 fields id and name. The table payments has id, id_clients, pay_month. The clients need to pay every month a certain amount. I want to search only the name of clients which has not paid the current month.
my query
SELECT name FROM clients WHERE id NOT IN (SELECT DISTINCT id_client FROM payments WHERE pay_month > '2014-03-15' ORDER BY pay_month DESC)
I managed to resolve my query. I used php to store the date $today = date("Y-m-d");
SELECT DISTINCT clients.id, clients.name FROM clients WHERE clients.id NOT IN (SELECT DISTINCT id_clients FROM payments WHERE pay_month > '".$today."' ORDER BY pay_month DESC) ORDER BY clients.name ASC
Thank you everyone for your help.
Maybe something like this.
SELECT
c.name as client_name,
p.pay_month as payment_month
FROM clients c
LEFT JOIN payments p on p.id_clients = c.id
WHERE p.id IS NULL AND YEAR(p.pay_month) >= YEAR(NOW()) AND MONTH(p.pay_month) > MONTH(NOW());
if you could give us some sample data i can build a sql fiddle and do it much easier. just edited to fix the date format/check