CODEWITHSUNDEEP
mysqlsql
gogasca
gogasca

Reputation: 10048

Create combinations from different tables

I have the following tables:

Table 1

name, weight
A, 4
B, 2
C, 1

Table 2

name, weight
AA, 4
BB, 2
CC, 1

Table 3

name, weight
AAA, 4
BBB, 2
CCC, 1

I want to create all combinations of tables and then order it by total weight (sum of weights). Example

A, AA, AAA, 12
A, BB, AAA, 10
A, CC, AAA, 9
A, AA, BBB, 10
A, BB, BBB, 8
A, CC, BBB, 7
A, AA, CCC, 9
A, BB, CCC, 7
A, CC, CCC, 6
...
C, CC, CCC, 3

Is it possible with SQL ?

Upvotes: 0

Views: 42

Answers (2)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17137

You're looking for a CROSS JOIN:

select  
  t1.name as name1, 
  t2.name as name2, 
  t3.name as name3,
  t1.weight + t2.weight + t3.weight as weight
from table1 t1
cross join table2 t2
cross join table3 t3

If your weight column might hold null values you will need coalesce function to avoid getting null as the output and treat those values as 0s :

select 
  t1.name as name1, 
  t2.name as name2, 
  t3.name as name3, 
  coalesce(t1.weight,0) + coalesce(t2.weight,0) + coalesce(t3.weight,0) as weight
from table1 t1
cross join table2 t2
cross join table3 t3

Upvotes: 2

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

It is possible with cross join

select 
  t1.name as name1, 
  t2.name as name2, 
  t3.name as name3, 
  t1.weight + t2.weight + t3.weight as weight
from table1 t1,
  table2 t2,
  table3 t3

Upvotes: 3

Related Questions