CODEWITHSUNDEEP
mysql
user139216
user139216

Reputation: 33

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 |

Upvotes: 3

Views: 123

Answers (1)

KuKeC
KuKeC

Reputation: 4620

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.

Upvotes: 1

Related Questions