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