Ask a Question
Ask Question Login
Corporate Training
  1. Community
  2. SQL Server
  3. Question
SQL Server

Can postgres case be used to select which table to join?

Asked by Augustina Fuentes Mar 13, 2023 1.1K views 1 answer
Share

About this question

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.

Your answer

1 Answer

More SQL Server discussions

Learn & Explore

Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.

Latest SQL Server Blogs

Guides, tips and career advice on SQL Server from JanBask experts.