MySQL dynamic JOIN

Question

Let's say that I have these tables on my db:

items
categories
weapons
shields
swords

And I need to create a join like this:

SELECT items.*, {swords}.*
FROM items
INNER JOIN categories
  ON categories.id = items.category_id # <-- here I know that the item is a sword
INNER JOIN {swords}
  ON {swords}.item_id = item.id
WHERE items.id = 12

But the {swords} part is dynamic since I found that an item is a sword checkgin the categories.name field.

The query will change if the categories.name is "shield" to this:

SELECT items.*, shields.*
FROM items
INNER JOIN categories
  ON categories.id = items.category_id
INNER JOIN shields
  ON shields.item_id = item.id
WHERE items.id = 13

I used { and } around the swords to show it like a variable

Thank you for your answer and sorry about my english! :)

Answer 1

Technically, you can only do this with dynamic SQL - which means MySQL's Prepared Statement syntax, or string concatenation to create the query prior to submitting it to the database. Prepared Statements are the preferable choice, due to better SQL injection defense.

You could use LEFT JOINs to the various tables, but it would mean including numerous columns from each of the tables (weapons, shields, swords...) that would be null if the category didn't match. It'd be horrible to try to pull out data from a single query...

PS: Why is there a SWORD table? Wouldn't that be covered by the WEAPON table?