List of clients by service

[nitaish]

I want to export list of clients in whmcs who has till now purchased only one service both active as well as inactive.

Can anyone help me with the SQL query?

[Kian]

SELECT t1.userid, CONCAT(t2.firstname, " ", t2.lastname) AS clientname FROM tblhosting AS t1 LEFT JOIN tblclients AS t2 ON t1.userid = t2.id GROUP BY t1.userid HAVING COUNT(*) = '1' 

 

[nitaish]

Can you also provide query which also gives the list of services the client has purchased till now?

[Kian]

SELECT t1.userid, CONCAT(t2.firstname, " ", t2.lastname) AS clientname, t1.id AS pid, CONCAT(t4.name, " > ", t3.name) AS productName FROM tblhosting AS t1 LEFT JOIN tblclients AS t2 ON t1.userid = t2.id LEFT JOIN tblproducts AS t3 ON t1.packageid = t3.id LEFT JOIN tblproductgroups AS t4 ON t3.gid = t4.id GROUP BY t1.userid HAVING COUNT(*) = '1'