MySQL CASE when SELECT

Question

I have the following query:

SELECT CASE WHEN `def_spell`.`type` = 0 THEN `spells_damage`.* 
            WHEN `def_spell`.`type` = 1 THEN `spells_weaken`.*
       END 
FROM `def_spell` 
LEFT JOIN `spells_damage` ON `def_spell`.`id` = `spells_damage`.`spell_id` 
LEFT JOIN `spells_weaken` ON `def_spell`.`id` = `spells_weaken`.`spell_id` 
WHERE `def_spell`.`id` = 1;

Hopefully that makes sense... I'm basically trying to select everything from spells_damage providing type is 0 and everything from spells_weaken providing type is 1.

I'd also like to select everything from def_spell regardless.

Can someone please fix the query? I've never used cases like this before and not sure how to.

SELECT CASE WHEN def_spell.type = 0 THEN spells_damage.* WHEN def_spell.type = 1 spells_weaken.* END FROM def_spell LEFT JOIN spells_damage ON def_spell.id = spells_damage.spell_id LEFT JOIN spells_weaken ON def_spell.id = spells_weaken.spell_id WHERE def_spell.id = 1; — Kryptix
– Kryptix, Commented Jun 8, 2012 at 3:03
I'm new to Stack Overflow, is there a way around it doing that? — Kryptix
– Kryptix, Commented Jun 8, 2012 at 3:04
I'm not sure SELECT CASE WHEN ... table1.* ELSE table2.* works (it *might if the tables have the same exact number of columns and atatypes. If it does, from a maintenance view it's terrible, and if you worked in my shop it wouldn't fly (neither does SELECT *). There has to be a better way, even if it means writing a longer SQL statement, to make this more readable and maintainable. — Ken White
– Ken White, Commented Jun 8, 2012 at 3:15

Jonathan Leffler · Accepted Answer · 2012-06-08 03:16:42Z

You can't use a CASE to choose between the output of the two tables like that.

Unfortunately, that's the easy bit; working out (a) what you're trying to do and (b) achieving an equivalent result is going to take a little longer.

It would be easier if you gave us information about the columns in the Spells_Weaken and Spells_Damage table. Presumably, there are some differences; otherwise, you'd have a single table. Indeed, a single Spells table might still be a better design.

Let's put doubts aside. Assuming that the Spells_Weaken and Spells_Damage tables are UNION-compatible, you can use a UNION to achieve the result:

SELECT s.*, d.*
  FROM def_spell AS s
  LEFT JOIN spells_damage AS d ON s.id = d.spell_id
 WHERE s.type = 0
   AND s.id = 1
UNION
SELECT s.*, w.*
  FROM def_spell AS s
  LEFT JOIN spells_weaken AS w ON s.id = w.spell_id 
 WHERE s.type = 1
   AND s.id = 1;

judda · Accepted Answer · 2012-06-08 03:13:05Z

You won't be able to do that. You will need to either split it into two queries, or manually specify each of the columns with the CASE statement.

SELECT CASE WHEN a.`type` = 0 THEN b.col1 
            WHEN a.`type` = 1 THEN c.col1
       END  AS col1
       , CASE WHEN a.`type` = 0 THEN b.col2
            WHEN a.`type` = 1 THEN c.col2
       END  AS col2
FROM `def_spell`  a
LEFT JOIN `spells_damage` b ON a.`id` = b.`spell_id` 
LEFT JOIN `spells_weaken` c ON a.`id` = c.`spell_id` 
WHERE a.`id` = 1;

Collectives™ on Stack Overflow

MySQL CASE when SELECT

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related