How to get all combinations satisfying a condition

Question

Given a table, Table, with columns N1, N2, N3, how can I get all combinations satisfying the condition N1 + N2 + N3 > 10?

For example, querying the table:

       N1      N2      N3
Row1   1       5       4
Row2   4       4       3

Should give the result:

       N1      N2      N3
Row1   4       5       4
Row2   4       4       4
Row3   4       4       3
Row3   4       5       3

How can I do this in T-SQL?

Answer 1

I haven't tested it but something like this should work. Cross join will give you all the combinations and you filter them to return only those which satisfy your condition. DISTINCT is there to filter duplicate combinations which may occur if i.e. all three columns have the same value in one row.

SELECT DISTINCT T1.N1, T2.N2, T3.N3
FROM
    (SELECT N1 FROM YourTable) AS T1
        CROSS JOIN
    (SELECT N2 FROM YourTable) AS T2
        CROSS JOIN
    (SELECT N3 FROM YourTable) AS T3
WHERE T1.N1 + T2.N2 + T3.N3 > 10;

Answer 2

If I've understood you correctly, you want all the combinations of N1, N2 and N3 which add up to > 10, regardless of which row the values occur on.

create table #t
(N1 int
,N2 int
,N3 int
)


insert #t
select 1,5,4
union select 4,4,3


select n1.N1, n2.N2, n3.N3
from #t as n1
cross join #t as n2
cross join #t as n3
where n1.N1 + n2.N2 + n3.N3 > 10

Answer 3

This is one of the very few examples of when a Cartesian product is a valid answer.

My query that I tried is as follows:

select distinct t1.t1, t2.t2, t3.t3
from test t1, test t2, test t3
where (t1.t1 + t2.t2 + t3.t3) > 10

I'm not familiar with the Cross Join syntax, but both seem to work fine.

Edit: I found this argument for the 'Join' syntax: http://stackoverflow.com/questions/128965/is-there-something-wrong-with-joins-that-don't-use-the-join-keyword-in-sql-or-mys