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.
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.
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