Help me Join - 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

General SQL Server Forums

New to SQL Server Programming

Help me Join

Author

Topic

venki.dmm
Starting Member

10 Posts

Posted - 2009-04-06 : 05:49:34

Hi All,

I am having the 2 tables 1)sales Table 2)purchase Table

Sales Table

Store_Name Sales
aa 10
bb 20
cc 30

Purchase Table

Store_Name Purchase
aa 10
dd 40

OutPut Required

Store_name Sales Purcahse
aa 10 10
bb 20 0
cc 30 0
dd 0 40

Can you please help me

Regards,
Venki

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 06:06:23

--Method 1

select Store_Name,max(sales) as sales, max(purchase) as purchase from
(
select Store_Name,sales,0 as purchase from sales
union all
select Store_Name,0, purchase from purchase
) as t
group by Store_Name
order by Store_Name

--Method 2
select coalesce(s.Store_Name,p.Store_Name),coalesce(s.Sales,0) as sales,coalesce(p.Purchase,0) as purchase from sales as s full join Purchase as p
on s.Store_Name=p.Store_Name order by 1

Madhivanan

Failing to plan is Planning to fail

venki.dmm
Starting Member

10 Posts

Posted - 2009-04-06 : 07:33:03

Hi Madhivanan
Thanks for your reply
iam using this Query.--Method 2
It is working fine but i am having the date column in both table
1)sales 2)purchase

when i am trying with date it is showing duplication columns

select coalesce(s.Store_Name,p.Store_Name),coalesce(s.Sales,0) as sales,coalesce(p.Purchase,0) as purchase from sales as s full join Purchase as p
on s.Store_Name=p.Store_Name order by 1

can you please help me

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-06 : 07:49:32

hi try this too,

select storename,isnull(sales,0) as sales,isnull(purchase,0) as purchase from
sales s left join purchase p
on s.storename = p.storepurchase
union
select storepurchase,isnull(sales,0) as sales,isnull(purchase,0) as purchase from
sales s right join purchase p
on s.storename = p.storepurchase

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 08:49:16

quote:
Originally posted by venki.dmm

Hi Madhivanan
Thanks for your reply
iam using this Query.--Method 2
It is working fine but i am having the date column in both table
1)sales 2)purchase

when i am trying with date it is showing duplication columns

select coalesce(s.Store_Name,p.Store_Name),coalesce(s.Sales,0) as sales,coalesce(p.Purchase,0) as purchase from sales as s full join Purchase as p
on s.Store_Name=p.Store_Name order by 1

can you please help me

Post the query that has problem

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 08:50:57

quote:
Originally posted by soorajtnpki

hi try this too,

select storename,isnull(sales,0) as sales,isnull(purchase,0) as purchase from
sales s left join purchase p
on s.storename = p.storepurchase
union
select storepurchase,isnull(sales,0) as sales,isnull(purchase,0) as purchase from
sales s right join purchase p
on s.storename = p.storepurchase

Full join actually does this

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources