Making 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

Making Join

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2012-10-24 : 09:40:10

Dear All,

I have a temp.table where i have lists of policyid.I need to sum all the price including the 'linkedpolicyid' price from policy table.
Temp table dosen't have linkedpolicyid.I need to find this and sum up together.

Eg:

PolicyiD price, linkedpolicyid(policy table)
1001 100.00 null
1003 50.00 5789
5789 20.20 null
1004 75.00 null

Policyid (#Temp table)
1001
1003

I wrote the below query

select sum(price) from policy
inner join #temp
on #temp.policyid = policy.policyid

Result
150.00

How to include/join the linkedpolicy here so that i can get the result as 170.20.

Any help will be highly appreciated!

Thanks
SG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 10:06:42

if its just one level of linking you can use a logic like


select sum(p1.price)+ SUM(p2.price) from policy p1
left join policy p2
ON p2.PolicyiD  - p1.LinkpolicyID
inner join #temp
on #temp.policyid = p1.policyid

if it spand multiple levels, you need a recursive CTE based logic

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satheesh
Posting Yak Master

152 Posts

Posted - 2012-10-24 : 10:43:55

Just one level of linking.
Many Thanks Visakh.Its working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 11:34:34

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources