Select different combinations for same table msSQL

Posted on

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

Leave a Reply

Your email address will not be published. Required fields are marked *