Dynamic table design (common lookup table), need a nice query to get the values
- by Swoosh
sql2005
This is my simplified example:
(in reality there are 40+ tables in here, I only showed 2)
I got a table called tb_modules, with 3 columns (id, description, tablename as varchar):
1, UserType, tb_usertype
2, Religion, tb_religion
(Last column is actually the name of a different table)
I got an other table that looks like this:
tb_value (columns:id, tb_modules_ID, usertype_OR_religion_ID)
values:
1111, 1, 45
1112, 1, 55
1113, 2, 123
1114, 2, 234
so, I mean 45, 55, 123, 234 are usertype OR religion ID's
(45, 55 usertype, 123, 234 religion ID`s)
Don't judge, I didn't design the database
Question
How can I make a select, showing * from tb_value, plus one column
That one column would be TITLE from the tb_usertype or RELIGIONNAME from the tb_religion table
I would like to make a general thing.
Was thinking initially about maybe a SQL function that returns a string, but I think I would need dynamic SQL, which is not ok in a function.
Anyone a better idea ?
