CODEWITHSUNDEEP
sqlitejoinconditional-statementssubquery
richard
richard

Reputation: 14540

Conditional subquery in SELECT statement

I'm trying to achieve the following here.

I've got 3 tables:

  1. blocks
  2. items
  3. ingredients

I query ingredients and depending on the result, I want to subquery or join some columns from blocks or items.

I managed to conditional subselect 1 column (name) in my select statement but if I want to select more I than one column, e.g. SELECT name, material_id, I get the following error:

Only a single result allowed for a SELECT that is part of an expression

I've seen many similar cases here on SO but can't manage to get this right.

SELECT
    `crafting_recipes`.`ingredient_id`, `crafting_recipes`.`ingredient_table`, 
    CASE
        WHEN
            `crafting_recipes`.`ingredient_table` = 'blocks' THEN 
                (SELECT `name` FROM `blocks` WHERE `id` = `crafting_recipes`.`ingredient_id`)
        ELSE 
            (SELECT `name` FROM `items` WHERE `id` = `crafting_recipes`.`ingredient_id`)
    END
FROM 
    `crafting_recipes`
WHERE
    `crafting_recipes`.`result_id` = 15 AND
    `crafting_recipes`.`result_table` = "blocks"

Since selecting multiple columns in a subselect isn't supported, what would be the correct way of achieving this?

Upvotes: 2

Views: 6796

Answers (1)

Donnie
Donnie

Reputation: 46943

I would do it along these lines to avoid the repeated correlated subqueries:

SELECT
  cr.`ingredient_id`, 
  cr.`ingredient_table`, 
  CASE
    WHEN
        cr.`ingredient_table` = 'blocks' THEN 
            b.`name`
    ELSE 
        i.`name`
  END as `name`
FROM
 `crafting_recipes` cr
  LEFT JOIN `blocks` b on b.`id` = cr.`ingredient_id`
  LEFT JOIN `items` i on i.`id` = cr.`ingredient_id`
WHERE
  cr.`result_id` = 15 AND
  cr.`result_table` = "blocks"

Upvotes: 3

Related Questions