August 7, 2008 at 4:08 am
Hi Experts,
I am a newbie in T-sql Programming.
Please help me.
I have two tables as below
Table1 (Table1 has data that are constant datas)
Group Data1 Data2 Data3
Table2
Name, Group, Data1, Data2, Data3
My result set should be as
Name, Group, Data1, Data2, Data3
Condition is that, if in the table2 i.e if column Data1 or data2 or Data3 have a value 1, in the result it have to fetch the corresponding data from table 1
Please help me writing a query for this.
August 7, 2008 at 4:33 am
I'm sorry, but I don't understand what you have to do.
Which data you have to retrieve from Table1?
The join condition is
Data1 = 1 OR Data2 = 1 OR Data3 = 1
or it was only an example? Did you mean the fields Data1/2/3 in Table1 have to be equal to Data1/2/3 in Table2?
Maybe you want to check this post:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- Gianluca Sartori
August 7, 2008 at 5:11 am
For Eg:
Table 1
groupdata1data2data3
ONE607080
TWO304050
Table2
Namegroupdata1data2data3
SASYONE100
SANYTWO010
Result
Namegroupdata1data2data3
SASYONE6000
SANYTWO0400
If data1,2,3, column is 1 , fetch its corresponding value from table1, if 0 remain same
August 7, 2008 at 5:40 am
Like this?
create table #t1 ([group] char(3), data1 int, data2 int ,data3 int)
create table #t2 ([name] char(4), [group] char(3), data1 int, data2 int ,data3 int)
insert into #t1 values('ONE', 60 , 70, 80)
insert #t1 values('TWO', 30 , 40, 50)
insert #t2 values('SASY', 'ONE', 1,0,0)
insert #t2 values('SANY', 'TWO', 0,1,0)
select t2.name, t2.[group],
case
t2.data1
when 1 then t1.data1
else t2.data1
end data1,
case
t2.data2
when 1 then t1.data2
else t2.data2
end data2,
case
t2.data3
when 1 then t1.data3
else t2.data3
end data1
from #t1 t1
inner join #t2 t2
on t1.[group] = t2.[group]
I strongly recommend you change the column name 'Group' to 'GroupId' or something similar. Group is a SQL keyword, as is Name, which is why I have had to escape them (surround the words with square brackets) in the code above.
HTH
Dave J
August 7, 2008 at 5:45 am
Thank you, it is working fine
I will try using your suggestion.
August 7, 2008 at 5:52 am
can you try the following way:
selectt2.Name
,t2.GroupName
,[Data1]= casewhen(t2.Data1 = 1)then t1.Data1
when(t2.Data1 = 0)then 0
end
,[Data2]= casewhen(t2.Data2 = 1)then t1.Data2
when(t2.Data2 = 0)then 0
end
,[Data3]= casewhen(t2.Data3 = 1)then t1.Data3
when(t2.Data3 = 0)then 0
end
from#table1 t1
leftouterjoin#table2 t2 on (t1.GroupName = t2.GroupName)
output:
-------
Name GroupName Data1 Data2 Data3
-------------------- ---------- ----------- ----------- -----------
ABCD ONE 60 0 0
XYZ TWO 0 40 0
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply