joined tables which are in a field of look up table

[nzvx]

Hi,

Can anyone help me about this query?

I have a master lookup table: member_profile.tbl
-------------------------------------------------
id |name | lookup_tbl |
-------------------------------------------------
1 | frequency | lu_frequency |
-------------------------------------------------
2 | ageGroup | lu_agegroup |
-------------------------------------------------
3 | numVisit | NULL | (<- this field means doesn't need to lookup)
-------------------------------------------------

and another 2+x tables, x depends on how many records in member_profile.tbl. In this case, x=2.

lu_frequency.tbl
--------------------------------
id |name |
---------------------------------
1 | Every month |
---------------------------------
2 | Every 2 months |
---------------------------------
3 | Every 3 months |
---------------------------------

lu_agegroup.tbl
---------------------------------
id |name |
---------------------------------
1 | 20-30 |
---------------------------------
2 | 31-40 |
---------------------------------
3 | 41-50 |
---------------------------------

member.tbl
---------------------------------
id |name |
---------------------------------
1 | Joe Blog |
---------------------------------
2 | John Smith |
---------------------------------

profile_detail.tbl
profile_id links to master lookup table member_profile to find which table need to be lookup,
and value is the id in that look up table.
-------------------------------------------------------------
id |member_id| profile_id | value |
--------------------------------------------------------------
1 | 2 | 2 | 2 | ( John Smith is in 31-40 age group)
--------------------------------------------------------------
2 | 2 | 1 | 1 | ( John Smith is in Every month group)
--------------------------------------------------------------
3 | 2 | 1 | 2 | ( John Smith is in Every 2 month group)
--------------------------------------------------------------
4 | 1 | 2 | 3 | ( Joe Blog is in 41-50 age group)
--------------------------------------------------------------
5 | 1 | 1 | 2 | ( Joe Blog is in Every 2 month group)
--------------------------------------------------------------
6 | 2 | 3 | 6 | ( John Smith visits somewhere 6 times)
--------------------------------------------------------------

How can i get result like below:
-------------------------------------------------------------------------------------------------
id |member_id | profile_id | value | lookup_tbl | name |
-------------------------------------------------------------------------------------------------
1 | 2 | 2 | 2 | lu_agegroup | 31-40 |
-------------------------------------------------------------------------------------------------
2 | 2 | 1 | 1 | lu_frequency | Every month |
-------------------------------------------------------------------------------------------------
3 | 2 | 1 | 2 | lu_frequency | Every 2 months |
-------------------------------------------------------------------------------------------------
4 | 1 | 2 | 3 | lu_agegroup | 41-50 |
-------------------------------------------------------------------------------------------------
5 | 1 | 1 | 2 | lu_frequency | Every 2 months |
--------------------------------------------------------------------------------------------------
6 | 2 | 3 | 6 | numVisit | NULL |
-------------------------------------------------------------------------------------------------

Query i can write is:

SELECT pd.`detail_id` AS id, pd.`member_id` , pd.`profile_id` , `value` , mp.lu_tbl, l.name
FROM `profile_detail` pd
CROSS JOIN member_profile mp ON pd.profile_id = mp.id
CROSS JOIN lu_frequency l ON l.id = pd.value

But I can't hard code that last join table as it should change on fly. Hope these make sense.

Thank in advance.

Richar

 

[r937]

richard, i would advise you to reconsider your design

see Tony Andrews on Oracle and Databases: OTLT and EAV: the two big design mistakes all beginners make and One True Lookup Table by some guy called Joe Celko

r937.com | rudy.ca