4

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.

1
  • Have a look at Dynamic SQL
    – McNets
    Apr 12 at 10:01

1 Answer 1

7

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.

4
  • I'd love to understand WHY the value the CASE evaluates to can't be used as the table name. From the comments on the question so far, it looks like Dynamic SQL can break this rule, though it looks to be ... more involved to get into & start understanding properly.
    – baudot
    Apr 12 at 12:04
  • 2
    Because a value like 42 or 'foobar' is just that: a value. A table name is an identifier. An identifier and a value are two very different things in SQL Apr 12 at 12:08
  • @baudot the value is obtained at run time; but to be able to run, the sql engine has to know which tables it will be using before running the query; and the way you want it to work, would require the query to execute for the first table, get all rows, and then go throught all of them running another query for each row. Even if this was possible, it will be VERY expensive.
    – Josh Part
    Apr 12 at 20:06
  • "It can't return the name of a table." Sure it can, but the rest of this is good.
    – Hart CO
    Apr 12 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.