Conditional Data Processing - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Conditional Data Processing

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-26 : 09:28:30

Bernard writes "I HAVE 2 TABLES
----------------
Table_1
----------------------------------
CODE | NAME |
----------------------------------
1-234 | ABC
1-432 | BCA
1-678 | DFG
1-674 | GFT

Table_2
----------------------------------
mex_CODE | mex_CODE_2|
----------------------------------
1-234 | 1-432
1-432 | 1-678
1-678 | 1-678
1-674 | 1-674

How do I get the CODE and CODE_2 in Table_2 to show as NAME like in Table_1?

I cannot just do a normal CASE-WHEN-THEN because there are too many records to specify manually so I am looking for a programtic approach.

I have started by joining the tables first (Table_1.CODE is related to Table_2.mex_CODE)

SELECT
T1.CODE AS TABLE1_CODE,
T1.NAME,
T2.mex_CODE AS TABLE2_CODE
FROM
Table_1 T1
LEFT JOIN
Table_2 T2
ON
T1.CODE = T2.mex_CODE

HOW DO I NOW GO ABOUT CHANGING THE TABLE2_CODE to THE NAME AS SHOWN IN TABLE1?"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-26 : 10:30:19

select t2.mex_CODE ,
mex_CODE_NAme = t1a.name ,
t2.mex_CODE_2 ,
mex_CODE_2_name = t1b.name
from Table_1a t1a
left join Table_2 t2 on t1a.CODE = t2.mex_CODE
left join Table_1a t1b on t1b.CODE = t2.mex_CODE_2

Could use inner join if it is always present.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources