mysql subquery for count is returning 0 [closed]

I am confused why its returning 0 in count_row

SELECT `id` , 
        (SELECT count(*) 
         FROM `user_credits` 
         WHERE `c_id` = `id` 
         LIMIT 1) as `count_row` 
FROM `user_lists`

it’s returning

enter image description here

Answer

I suspect that the table user_credits has a column named id, so the comparison: c_id = id compares the columns id and c_id of the same table.
Use an alias for the table user_lists and use it to qualify the column id insid the subquery:

SELECT u.`id` , 
        (SELECT count(*) 
         FROM `user_credits` AS c 
         WHERE c.`c_id` = u.`id`) as `count_row` 
FROM `user_lists` AS u

Also, the subquery returns only 1 row even without LIMIT 1.

Leave a Reply

Your email address will not be published. Required fields are marked *