dohdle February 2016

MySQL Issue | Subquery returns more than 1 row

$SQL->query('SELECT `p`.`name` AS  `name`,
  (SELECT `k`.`value` FROM `player_storage` k LEFT JOIN `players` p ON `k`.`player_id` = `p`.`id` WHERE `k`.`key` = 11000) AS value
   FROM `player_storage` k, `players` p
   WHERE `k`.`player_id` = `p`.`id`
   GROUP BY  `name` 
   ORDER BY CAST(`value` AS SIGNED) 
   DESC LIMIT 0 , 30
   ;')

Error: Subquery returns more than 1 row

Since i have no knowledge in mysql can someone please help me?

I trying to take 2 values from the same column key = 11000 key = 11001

Answers


Zafar Malik February 2016

As error clearly shows your sub-query is returning 2 rows against single row in main query, how is it possible so you are getting error.

You can get result by below query-

SELECT `p`.`name` AS  `name`,
 `p`.`skull` AS `skull`,
  (SELECT `k`.`value` FROM `player_storage` k LEFT JOIN `players` p ON `k`.`player_id` = `p`.`id` WHERE `k`.`key` = 11000 LIMIT 1) AS VALUE
   FROM `player_storage` k, `players` p
   WHERE `k`.`player_id` = `p`.`id`
   GROUP BY  `name` 
   ORDER BY CAST(`value` AS SIGNED) 
   DESC LIMIT 0 , 30

Note: Not sure you require same results or something else but it will help you get the cause of issue.


Jordan February 2016

Try this

select `p`.`name` AS  `name`,
  (
    select `k`.`value` from `player_Storage` k
    where `k`.`key` =11000 and `k`.`player_id` not in (select `p` .`id` from `player` p)
  ) AS value
   FROM `player_storage` k, `players` p
   WHERE `k`.`player_id` = `p`.`id`
   GROUP BY  `name` 
   ORDER BY CAST(`value` AS SIGNED) 
   DESC LIMIT 0 , 30;

Post Status

Asked in February 2016
Viewed 1,902 times
Voted 6
Answered 2 times

Search




Leave an answer