Mysql cross matrix

Question

I have a table with three columns Start, End, Result. The data in my table is like

Start   End                      Result
1        2                       1,2
1        3                       1,3
1        4                       1,4
1        5                       1,5
1        N  (n means 6,7,…etc)   1,n
2        3                       2,3
2        4                       2,4
2        5                       2,5
2        6                       2,6
2        N  (n means,7,…etc)     2,n
3        4                       3,4
3        5                       3,5
3        6                       3,6
3        N  (n means 7,…etc)     3,n
4        5                       4,5
4        6                       4,6
4        N  (n means 6,7,…etc)   4,n
5        6                       5,6
5        N (n means 7,…etc)      5,n
.................................
N (n means 6,7,…etc)    N (n means 7,…etc)  N,N

Now I give two values like 1,6

Then I want to get (1,2) ,(1,3),(1,4),(1,5),(1,6), (2,3),(2,4),(2,5),(2,6), (3,4),(3,5),(3,6),(4,5) ,(4,6),(5,6)

Now I give two values like 2,3

Then I want to get (1,3) ,(1,4),(1,5) ,(1,6),(2,3) ,(2,4),(2,5),(2,6)

Now I give two values 5,6

Then I want to get (1,6), (2,6), (3,6), (4,6),(5,6)

And Vise Versa.

Here how we create the MySQL query for this problem.

Answer 1

Select Result from my_table where End > 1 AND End <= 6

Select Result from my_table where End > 2 AND END <= 3

Select Result from my_table where End > 5 AND END <= 6

Here is a sample like the data you already mentioned above: https://sqliteonline.com/#fiddle-5b470f33ec077u5jjia9av4 you should be able to see the results of the query.