Can CASE be used to select which table to JOIN?



  • I'm attempting to use a CASE expression to select which table each row should be joined with.

    Say I've got a table of item_instances in my game, of all the items in the game world, and some of the items are cookie-cutter copies of generic items from the item_templates table, and other items started off as templates, and then acquired unique properties as players used them. They are now stored in the unique_items table.

    So when I find a record of an item in the item_instances table, I want to look up more information about it, and I need to pull that info from the correct table.

    This is the sort of thing I've been trying, without success:

    SELECT item_table, item_id, *
    FROM item_instances AS ii
    JOIN
    CASE ii.item_table
        WHEN 0 THEN 'item_templates'
        WHEN 1 THEN 'unique_items'
        ELSE 'unique_items'
    END
    ON CASE = ii.item_id;
    

    If there's a quick fix for the syntax, I'd love to hear it. Or if this is something you can't do conceptually - have each row choose its own join - I'd love to get a link to a deeper explanation.

    In the past I've done operations like this by doing two SELECTS, one of which is against the item_templates table, the other against the unique_items table, and taken their UNION. This feels like a more correct & less wasteful way to do it. If it's not for some reason to do with SQL's deeper architecture, I'd like to understand why.



  • A CASE expression returns a single value. It can't return an identifier (e.g. a table reference). And its value can't be used instead of a table name

    The typical way to deal with this kind of optional dependencies, is to join to each table and include the condition that picks a table in the join conditions. In order to get rows back, you will need an outer join because you can't know in which table there will be a match

    SELECT item_table, item_id, *
    FROM item_instances AS ii
      LEFT JOIN item_templates it ON ii.item_id = it.item_id and ii.item_table = 0
      LEFT JOIN unique_items ui ON ii.item_id = ui.item_id and ii.item_table = 1
    

    You can then use coalesce() in the SELECT list, to get the non-null values from either table.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2