user139216 user139216 - 6 days ago 7
MySQL Question

Mysql Query for Combinations

can anyone please guide me with writing MySQL query for following scenario.

The data in table is like this,

Table Name: Vals

V1 | V2 | V3 |
+-----------+----+---------+
| 143 | 1 | 1 |
| 2003 | 2 | 6 |


I want result to be like this which is basically Combinations of columns with particular Column constant.

V1 | V2 | V3 |
+-----------+----+---------+
| 143 | 1 | 1 |
| 143 | 1 | 6 |
| 143 | 2 | 1 |
| 143 | 2 | 6 |
| 2003 | 1 | 1 |
| 2003 | 1 | 6 |
| 2003 | 2 | 1 |
| 2003 | 2 | 6 |

Answer

You need to use something like this do get all combinations

SELECT DISTINCT a.V1,
                b.V2,
                c.V3
FROM Vals a,
     Vals b,
     Vals c

To get it sorted then you add ORDED BY and then query looks like

SELECT DISTINCT a.V1,
                b.V2,
                c.V3
FROM Vals a,
     Vals b,
     Vals c
ORDER BY 1,
         2,
         3

Tested it on my table and it worked, hope it helps you.