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.