doublea2k7 Posted September 3, 2010 Share Posted September 3, 2010 Current Mysql Version I have a table that holds user account info. I have another table that holds status updates according to their account "id" in the clients table. Example |Table clients| id Name Last Name 1 Fred John 2 Frank Joe |Table Status| id status clientId 0 In Progress 1 1 Still In Progress 1 I want to be able to select all accounts with no entries in the status table. Here is what I have but its not working. SELECT id, name FROM clients as u INNER JOIN ( SELECT clientId, COUNT(*) AS statusCount FROM status HAVING statusCount > '0' ) AS statusSubQuery ON (statusSubQuery.clientId = u.id) "; Link to comment https://forums.phpfreaks.com/topic/212410-mysql-subquery-help/ Share on other sites More sharing options...
kickstart Posted September 3, 2010 Share Posted September 3, 2010 Hi 2 issues I can see. Firstly you subselect has no group by clause, hence the count and clientid are going to not be useful (MySQL will probably complain about them). Secondly you are using an INNER JOIN, so any clientid that doesn't appear on the subselect (ie, any with a count of 0) will not result in a record after the join. If you do not really care about the count, just that it is 0 SELECT id, name FROM clients as u LEFT OUTER JOIN status s ON s.clientId = u.id WHERE s.clientId IS NULL All the best Keith Link to comment https://forums.phpfreaks.com/topic/212410-mysql-subquery-help/#findComment-1106751 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.