All we need is an easy explanation of the problem, so here it is.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂