Can CASE be used to select which table to JOIN?

by

The question:

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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

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.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment