Help in Query - 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 2005 Forums

Transact-SQL (2005)

Help in Query

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-18 : 07:47:01

Table1

Name Stat CULT
XYZ 90 INDI
ABC 50 ENGL
XYZ 10 IRIS

Table2

SCode INDI ENGL IRIS
315 100 700 200
456 346 234 40

My Input is Cname = 'XYZ' and SCode = '315'. Here the Stat value of XYZ should multiply with corresponding column
in table2 with respect to CULT column in Table1

XYZ (INDI)=90 (from stats) times 100 (from country of origin)=9000
XYZ (ENGL)=10 (from stats) times 200 (from country of origin)=2000

Need to come output value as

INDI
9000

G. Satish

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-18 : 08:44:58

Read about Normalization

Try

SELECT Name,Stat*case when t1.CULT='INDI' then INDI when t1.CULT='ENGL' then ENGL when t1.CULT='IRIS' then IRIS end
from table1 as t1 inner join table2 as t2 on 1=1
where t1.Name='XYZ' and t2.Scode=315

Madhivanan

Failing to plan is Planning to fail

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-18 : 09:09:12

Thank you. This works perfect.

quote:
Originally posted by madhivanan

Read about Normalization

Try

SELECT Name,Stat*case when t1.CULT='INDI' then INDI when t1.CULT='ENGL' then ENGL when t1.CULT='IRIS' then IRIS end
from table1 as t1 inner join table2 as t2 on 1=1
where t1.Name='XYZ' and t2.Scode=315

Madhivanan

Failing to plan is Planning to fail

G. Satish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources