CODEWITHSUNDEEP
phpmysqlsql
mohammad ali Payan
mohammad ali Payan

Reputation: 43

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

Upvotes: 1

Views: 67

Answers (1)

Shadow
Shadow

Reputation: 34285

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.

Upvotes: 2

Related Questions