Question :
I have a table which has 2 fields OS_ID
and OZ_ID
. I want to select combinations of OS_ID
for different OZ_ID
. What would be the query for that?
Input
OS_ID OZ_ID
---- ----
1 1
2 1
3 2
4 2
5 2
6 2
7 3
8 3
9 3
Output
combination row
1,3,7
1,3,8
1,3,9
1,4,7
1,4,8
1,4,9
1,5,7
1,5,8
1,5,9
1,6,7
1,6,8
1,6,9
2,4,7
2,4,8
...
Answer :
Looks like you want Cross Joins:
SELECT t1.OS_ID, t2.OS_ID, t3.OS_ID
FROM tab AS t1
CROSS JOIN tab AS t2
CROSS JOIN tab AS t3
WHERE t1.OZ_ID = 1
AND t2.OZ_ID = 2
AND t3.OZ_ID = 3