sum of any two value match - 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

sum of any two value match

Author

Topic

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2015-02-08 : 14:46:27

hi team,

I have a table having single column id and n number of rows and 1 want to know all combination of id's where sum or subtraction of any two id's is equal to 7.

id
1
2
3
4
5
6
9
12
18
..
..

so output should be like

2 5
3 4
17 10
.......

please help.

prithvi nath pandey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-08 : 15:24:14

Here you go:

declare @t table (id int)

insert into @t values
(1),
(2),
(3),
(4),
(5),
(6),
(9),
(10),
(12),
(17),
(18)

select * from (
select t1.id as t1id, t2.id as t2id
from @t t1
cross join @t t2
) t
where t1id + t2id = 7 or t2id - t1id = 7 or t1id - t2id = 7

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-08 : 16:31:57

Alternative:

select *
  from yourtable as a
       inner join yourtable as b
               on 7 in (a.id+b.id,a.id-b.id,b.id-a.id)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-09 : 03:54:16

Do you want permutations or combinations?

2 5
5 2

or only

2 5

??

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

pradeepbliss
Starting Member

28 Posts

Posted - 2015-02-09 : 05:11:15

quote:
Originally posted by nextaxtion

hi team,

I have a table having single column id and n number of rows and 1 want to know all combination of id's where sum or subtraction of any two id's is equal to 7.

id
1
2
3
4
5
6
9
12
18
..
..

so output should be like

2 5
3 4
17 10
.......

please help.

prithvi nath pandey

create procedure [dbo].[SP_Addition]
as
begin
declare @i int
declare @j int
declare @k int
select @k = max(id) from @YourTableName
set @i = 1
FirstLoop:
if @i <= @k
begin
set @j= @i + 1
SecondLoop:
if @i + @j = 7
begin
select @i as 'Col-1',@j as 'Col-2'
set @i= @i + 1
goto FirstLoop
end
else
begin
set @j= @j + 1
if @j < @k
begin
goto SecondLoop
end
else
goto ThirdLoop
end
end
ThirdLoop:
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources