table joining output - 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)

table joining output

Author

Topic

pkindigo
Starting Member

2 Posts

Posted - 2010-08-12 : 02:17:23

hi,

I have two tables like

create table a(id int, expense int)

insert into a

select 1,2000
union all
select 1,2000
union all
select 2,3000
union all
select 2,3000

create table b(id int, amount int)

insert into b

select 1,5000
union all
select 2,10000

i write the query ---
select
a.id , a.expense
,b.id , b.amount
from a
inner join b on b.id = a.id

it return the result -----
1 2000 1 5000
1 2000 1 5000
2 3000 2 10000
2 3000 2 10000

but i want -------------
1 2000 1 5000
1 2000 1 0
2 3000 2 10000
2 3000 2 0

please help me
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-12 : 02:34:13

That makes no sense - sorry.

No, you're never too old to Yak'n'Roll if you're too young to die.

urzsuresh
Starting Member

30 Posts

Posted - 2010-08-12 : 07:07:18

Hello friend,
Kindly try this below sample one. You will get some idea. Let me know that, do you need any further clariffication

declare @a table (id int, expense int)

insert into @a

select 1,2000
union all
select 1,2000
union all
select 2,3000
union all
select 2,3000

declare @b table (id int, amount int)

insert into @b

select 1,5000
union all
select 2,10000

;with cte
as
(
select
a.id , a.expense
,b.id ids , b.amount,row_number() over( partition by b.amount order by newid())as Result
from @a a
inner join @b b on b.id = a.id
),cte1 as
(
Select a.id,a.expense,a.ids,0 amount,a.result
from cte a
Join cte b on a.id=b.id and b.result=2 and a.result=2
union all Select id,expense,ids,amount,result from cte where result=1
)
Select id,expense,ids,amount from cte1 order by ids,result

quote:
Originally posted by pkindigo

hi,

I have two tables like

create table a(id int, expense int)

insert into a

select 1,2000
union all
select 1,2000
union all
select 2,3000
union all
select 2,3000

create table b(id int, amount int)

insert into b

select 1,5000
union all
select 2,10000

i write the query ---
select
a.id , a.expense
,b.id , b.amount
from a
inner join b on b.id = a.id

it return the result -----
1 2000 1 5000
1 2000 1 5000
2 3000 2 10000
2 3000 2 10000

but i want -------------
1 2000 1 5000
1 2000 1 0
2 3000 2 10000
2 3000 2 0

please help me
Thanks

Suri

urzsuresh
Starting Member

30 Posts

Posted - 2010-08-12 : 07:14:48

Hello Friend,
This another method
declare @a table (id int, expense int)

insert into @a

select 1,2000
union all
select 1,2000
union all
select 2,3000
union all
select 2,3000

declare @b table (id int, amount int)

insert into @b

select 1,5000
union all
select 2,10000

;with cte
as
(
select
distinct a.id , a.expense
,b.id ids , b.amount,row_number() over( partition by b.amount order by newid())as Result
from @a a
inner join @b b on b.id = a.id
),cte1 as
(
Select distinct a.id,a.expense,a.ids,Case a.Result when 2 then 0 else a.amount end amount, a.result
from cte a

)
Select id,expense,ids,amount from cte1 order by ids,result

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources