Search code examples
mysqldatabasedefault

How to set default value in SubQuery result using mysql

How to set default VALUE in SubQuery result using mysql

SELECT      
    p.`id`, p.`name`, p.`class_name`, cpd.`status_team`,
    cpd.`home`, cpd.`guest`, cpd.`mvp`, cpd.`oscar`,
    cpd.`wam`, cpd.`status`, cpd.`added_date`,
    (SELECT result FROM result_cards WHERE `id` = cpd.`result`) AS DEFAULT(`result`)
FROM `cron_players_data` cpd
INNER JOIN `players` p ON cpd.`player_id` = p.id
WHERE cpd.`added_date` = '2012-03-29' AND cpd.team_id = '15'

when i remove this DEFAULT() the Query will execute normally. Actually i want by default result value is 0 or helps are definitely appreciated

enter image description here

Solution

  • You should move the sub query to a join. But I am not able to understand what you are trying to do with DEFAULT(). You will need to explain what you are trying to achieve.

    SELECT      
    p.`id`, p.`name`, p.`class_name`, cpd.`status_team`,
    cpd.`home`, cpd.`guest`, cpd.`mvp`, cpd.`oscar`,
    cpd.`wam`, cpd.`status`, cpd.`added_date`,
    IFNULL(rc.`result`, 0) AS `result`
FROM `cron_players_data` cpd
INNER JOIN `players` p
    ON cpd.`player_id` = p.id
LEFT JOIN result_cards rc
    ON cpd.`result` = rc.id
WHERE cpd.`added_date` = '2012-03-29'
AND cpd.team_id = '15'