CODEWITHSUNDEEP
mysql
user892134
user892134

Reputation: 3224

Subquery in select statement returning SQL error

I get an SQL error for this query

Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*)
FROM `cast_gallery` q
JOIN `all_media` r on q.all_media_id=r.id
WHERE q.pa' at line 1

SELECT `cast_name`, `cast_slug`, (SELECT count(q.*)
 FROM `cast_gallery` q
 JOIN `all_media` r on q.all_media_id=r.id
 WHERE q.parent = `casts`.`id` AND r.approved=1) as image_gallery, `active`, `view`, `checked`, `sex`, `id`
FROM (`casts`)
ORDER BY `cast_name` asc
LIMIT 10

How to solve?

Upvotes: 1

Views: 69

Answers (4)

Mahesh Hegde
Mahesh Hegde

Reputation: 1209

you can try instead of count(q.*), you can use q.all_media_id

SELECT `cast_name`, `cast_slug`, (SELECT count(q.all_media_id)
 FROM `cast_gallery` q
 JOIN `all_media` r on q.all_media_id=r.id
 WHERE q.parent = `casts`.`id` AND r.approved=1) as image_gallery, `active`, `view`, `checked`, `sex`, `id`
FROM (`casts`)
ORDER BY `cast_name` asc
LIMIT 10

Upvotes: 0

curious_dev
curious_dev

Reputation: 36

The Syntax for count is incorrect. If we want to find out the number of rows in subquery we can use count(*). Please refer to http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/sql_func_count.asp.html for more clarifiction.

SELECT `cast_name`, `cast_slug`, (SELECT count(*)
FROM `cast_gallery` q
JOIN `all_media` r on q.all_media_id=r.id
WHERE q.parent = `casts`.`id` AND r.approved=1) as image_gallery, `active`, 
`view`, `checked`, `sex`, `id`
FROM (`casts`)
ORDER BY `cast_name` asc
LIMIT 10

should work

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

To count total number of rows, we use COUNT(*), not Count(q.*). Also, there is no need of parentheses around casts in the From clause.

SELECT `cast_name`, 
       `cast_slug`, 
       (SELECT count(*)
        FROM `cast_gallery` q
        JOIN `all_media` r on q.all_media_id=r.id
        WHERE q.parent = `casts`.`id` AND 
              r.approved=1) as image_gallery, 
      `active`, 
      `view`, 
      `checked`, 
      `sex`, 
      `id`
FROM `casts`
ORDER BY `cast_name` asc
LIMIT 10

Additional Info:

COUNT(...) is used to count the number of non-NULL values; however COUNT(*) will count the total number of rows retrieved, whether or not they contain NULL values.

Upvotes: 1

Jignesh Joisar
Jignesh Joisar

Reputation: 15115

try to just change count(q.*) to count(*)

Upvotes: 1

Related Questions