Item table
id key code description
------------------------------
1 1 misc miscellaneous
2 1 med medicine
Miscellaneous table:
id code description
------------------------
1 misc1 miscellaneous
2 misc1 miscellaneous
Medicine table:
id code description
---------------------------
1 medicine1 medicine
2 medicine1 medicine
I have this table structure; my main table is the Item table and I want to JOIN the main table with other table based on the column value in main table. The column that determines the table to be joined is code. If code is misc join with misc table if value is med join with medicine table.
I know the basic JOIN of table like
SELECT *
FROM item
INNER JOIN miscellaneous ON item.key = miscellaneous.id
But I don't know how to join when there is a condition that will point to which table to JOIN