Mysql subquery help

[doublea2k7]

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)
";

 

[kickstart]

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