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