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 2008 Forums
 Transact-SQL (2008)
 Left Join issue with duplicate rows

Author  Topic 

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2014-07-23 : 07:06:02
Hi,

I have the below select statement (simplified) - with a Left Join which pulls back a currency amount for the rows.

But I have now discovered that in some cases there may be rows duplicated in the database (based on 'asset' field) but this is how it is supposed to be.

As a result I am trying to figure out a way of only assigning one currency amount per duplicated row and a zero amount for the extra duplications. So, currently with the below SQL, the asset 'Asset25' is returned twice and therefore summed as a total value of €10 (i.e. €5 + €5) when its value is really €5.

How could I fix this?

Thanks for any help...

SELECT b.amount, * FROM database1 a LEFT JOIN database2 b on b.asset_id = a.asset

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-07-23 : 08:14:06
Something like this may work

select case when sno=1 then amount else 0 end as amount, t.othercolumns from
(
SELECT row_number() over (partition by a.asset order by a.asset) as s_no,b.amount, * FROM database1 a LEFT JOIN database2 b on b.asset_id = a.asset
) as t


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2014-07-23 : 08:29:30

amazing work...thanks for that...would have never been able to put it together like that.
Go to Top of Page
   

- Advertisement -