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
 New to SQL Server Programming
 Query Help

Author  Topic 

wisko
Starting Member

8 Posts

Posted - 2011-07-15 : 00:56:45
Hi All

i need to query data from two tables & get them into one column. the condition should be, Table1 ID's team value will prompt unless Table2 got a value for that particular ID.


Table1
======
ID Name Team
---------------------
1 A X
2 B Y
3 C Z



Table2
======
ID Team
--------------------
1 M
3 N


Result should be
 
ID Team
--------------------
1 M
2 Y
3 N

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-15 : 01:00:23
Try this (untested):

select t1.id, team = case when t2.id is not null then t2.team else t1.team end
from table1 t1
left join table2 t2
on t1.id = t2.id


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

wisko
Starting Member

8 Posts

Posted - 2011-07-15 : 01:41:50
quote:
Originally posted by tkizer

Try this (untested):

select t1.id, team = case when t2.id is not null then t2.team else t1.team end
from table1 t1
left join table2 t2
on t1.id = t2.id


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog


thanks tara,but it gives the result as like this

ID Team
--------------------
1 M
2 Y
2 Y

3 N
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-15 : 02:07:23
No it doesn't. I've now tested it. Here' my test:


declare @t1 table (id tinyint, team char(1))
declare @t2 table (id tinyint, team char(1))

insert into @t1 values(1, 'x')
insert into @t1 values(2, 'y')
insert into @t1 values(3, 'z')

insert into @t2 values(1, 'M')
insert into @t2 values(3, 'N')

select t1.id, team = case when t2.id is not null then t2.team else t1.team end
from @t1 t1
left join @t2 t2
on t1.id = t2.id


My query returns this:

id team
---- ----
1 M
2 y
3 N


So something must be wrong with your data or your sample is wrong. Let me know.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

wisko
Starting Member

8 Posts

Posted - 2011-07-15 : 04:34:43
quote:
Originally posted by tkizer

No it doesn't. I've now tested it. Here' my test:


declare @t1 table (id tinyint, team char(1))
declare @t2 table (id tinyint, team char(1))

insert into @t1 values(1, 'x')
insert into @t1 values(2, 'y')
insert into @t1 values(3, 'z')

insert into @t2 values(1, 'M')
insert into @t2 values(3, 'N')

select t1.id, team = case when t2.id is not null then t2.team else t1.team end
from @t1 t1
left join @t2 t2
on t1.id = t2.id


My query returns this:

id team
---- ----
1 M
2 y
3 N


So something must be wrong with your data or your sample is wrong. Let me know.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



oops my bad...

got it perfectly.. thanks a lot tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-15 : 12:20:12
You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -