mohammad ali Payan mohammad ali Payan - 7 months ago 17
SQL Question

How do i fix subquery returns more than 1 row error

I am executing this query

$sql=mysql_query("SELECT c.*,
(SELECT Count(b.text) From tbl_people_comment Where b.idsms=c.id AND b.visible=1) AS ccomment
FROM tbl_peoplesms AS c
INNER JOIN tbl_people_comment AS b ON b.idsms= c.id
WHERE c.visible=1
GROUP BY c.id");


I am trying to make it return something like this in php

[
{
"id":"20",
"name":"test",
"text":"test",
"visible":"1",
"ccomment":"5"
},

{
"id":"19",
"name":"test",
"text":"test",
"visible":"1"
"ccomment":"7"
}
]


i use this code to show it

while($row=mysql_fetch_assoc($sql))
$output[]=$row;
print(json_encode($output, JSON_UNESCAPED_UNICODE));


However, I am getting the error Subquery returns more than 1 row.

this is tbl_peoplesms
tbl_peoplesms

and tbl_people_comment
tbl_people_comment

Answer

Instead of using an inline subquery, just left join tbl_people_comment on tbl_peoplesms and move the b.visible=1 condition into the join clause:

SELECT c.name, count(b.text) as ccomment
FROM tbl_peoplesms AS c
LEFT JOIN tbl_people_comment AS b ON b.idsms= c.id and b.visible=1
GROUP BY c.name

If you would like to include more fields from the tbl_people_comment table in the select list, then add them to the group by clause as well.

Comments