How to create algorithm or formula to get the combinations?

Question

i want to create an algorithm or formula that gives me the following combinations below. I have manually printed out all the combinations for the example containing 4 tables with respective values.

This is not permutation because i need the combinations to always follow the unique format

Table1,Table2,Table3,Table4 values

So how can i achieve this in SQL script or through C# code (VB.Net code can also work)

Note: the solution contain all the possible 48 combinations i need for my problem.

Problem

Table1     Table2     Table3     Table4
a1             b1           c1             d1
a2             b2           c2             d2
a3                            c3
                                c4

Solution

a1,b1,c1,d1
a1,b1,c1,d2
a1,b1,c2,d1
a1,b1,c2,d2
a1,b1,c3,d1
a1,b1,c3,d2
a1,b1,c4,d1
a1,b1,c4,d2

a1,b2,c1,d1
a1,b2,c1,d2
a1,b2,c2,d1
a1,b2,c2,d2
a1,b2,c3,d1
a1,b2,c3,d2
a1,b2,c4,d1
a1,b2,c4,d2

a2,b1,c1,d1
a2,b1,c1,d2
a2,b1,c2,d1
a2,b1,c2,d2
a2,b1,c3,d1
a2,b1,c3,d2
a2,b1,c4,d1
a2,b1,c4,d2

a2,b2,c1,d1
a2,b2,c1,d2
a2,b2,c2,d1
a2,b2,c2,d2
a2,b2,c3,d1
a2,b2,c3,d2
a2,b2,c4,d1
a2,b2,c4,d2

a3,b1,c1,d1
a3,b1,c1,d2
a3,b1,c2,d1
a3,b1,c2,d2
a3,b1,c3,d1
a3,b1,c3,d2
a3,b1,c4,d1
a3,b1,c4,d2

a3,b2,c1,d1
a3,b2,c1,d2
a3,b2,c2,d1
a3,b2,c2,d2
a3,b2,c3,d1
a3,b2,c3,d2
a3,b2,c4,d1
a3,b2,c4,d2

@anirudh4444: Two answers below show it can be done. In fact this is a classical use of the set theory that underpins SQL — gbn, Jun 11 '11 at 20:14

GSerg · Accepted Answer · 2011-06-11T19:55:07.730

6

This is called Cartesian product.

select *
from
  table1
  cross join table2
  cross join table3
  cross join table4

Same thing:

select *
from
  table1, table2, table3, table4

edited Jun 11 '11 at 19:55

answered Jun 11 '11 at 19:02

GSerg

76,472
17
159
346

`CROSS APPLY` would also return the full cartesian product – Duncan Howe Jun 11 '11 at 19:05
@Duncan Howe: CROSS APPLY is *not* for this. @GSerg: Your first solution should be CROSS JOIN – gbn Jun 11 '11 at 19:34
@gbn My bad, for some reason I thought the cross was default if omitted. – GSerg Jun 11 '11 at 19:55
@GSerg: could be on some RDBMS of course, but with .net I assumed SQL Server :-) – gbn Jun 11 '11 at 19:59
@gbn: Just FYI, there is an RDBMS that treats INNER [JOINs](http://dev.mysql.com/doc/refman/5.0/en/join.html) same as CROSS JOINs. An excerpt from the linked article: `In [the said RDBMS], CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.` – Andriy M Jun 11 '11 at 20:21
@Andriy M: Thanks, *another* MySQL ambiguity... http://stackoverflow.com/questions/6060241/which-is-the-least-expensive-aggregate-function-in-the-absence-of-any/6060419#6060419 – gbn Jun 11 '11 at 20:23

score 4 · Answer 2 · answered Jun 11 '11 at 19:32

4

Use CROSS JOIN (rather then the usual INNER or LEFT OUTER JOINs)

select *
from
  table1
  cross join table2
  cross join table3
  cross join table4

or the implied JOIN as per GSerg's answer ...from table1, table2, table3, table4

answered Jun 11 '11 at 19:32

gbn

422,506
82
585
676

How to create algorithm or formula to get the combinations?

2 Answers2