Jump to content

Mysql subquery help


doublea2k7

Recommended Posts

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.