List of clients by invoices

[nitaish]

I want to export list of clients in whmcs who has more than 2 invoices unpaid.

Can anyone help me with the SQL query?

[brian!]

I would have thought the following should return what you want...

SELECT tblclients.*
FROM tblinvoices
JOIN tblclients ON tblinvoices.userid = tblclients.id 
WHERE tblinvoices.`status` = 'Unpaid' 
GROUP BY tblinvoices.userid 
HAVING count(tblinvoices.userid) >= 2

i'm not sure what you want in terms of clients details, so i'm returning everything! you can tweak the select to specify which columns you want to return in the results.

Edited August 25, 2020 by brian!