Table Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Table Join

Author

Topic

Syima
Starting Member

13 Posts

Posted - 2007-09-07 : 00:52:53

Hi there, anyone can help me to join Table_1 & Table_2 to get below output: Thanks.

Table_1
id, mbb_benefitcategory, mbb_projecttype
5001369, lkp_val_growth, lkp_val_appl

Table_2
id, lookup_type, lookup_code, name
500, mbb_prj_benefitcategory, lkp_val_growth, growth
501, mbb_prj_projecttype, lkp_val_appl, application

Output:
Table_1.id, Table_2.Name as A, Table_2.Name as B
5001369, growth, application

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 02:34:28

You may want to search for OTLT (One True Lookup Table)


SELECT T1.id, BC.name, PT.name
FROM Table_1 AS T1
     JOIN Table_2 AS BC
          ON BC.lookup_type = 'mbb_prj_benefitcategory'
         AND BC.lookup_code = T1.mbb_benefitcategory
     JOIN Table_2 AS PT
          ON PT.lookup_type = 'mbb_prj_projecttype'
         AND PT.lookup_code = T1.mbb_projecttype

Kristen

Syima
Starting Member

13 Posts

Posted - 2007-09-07 : 03:57:55

Hi Kristen

TQ very much for ur help. It works. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources