CODEWITHSUNDEEP
mysqlsqlsql-server-2008
Zcorps
Zcorps

Reputation: 102

how to generate number of possible combination in sql query

I am confused with the following question - I have two tables,

Table A (A1,A2,A3,A4)
TABLE B (B1,B2,B3,B4,B5)

What will be the query to generate all possible combinations of 2 rows from table A and 3 rows of table B. For example - I can have a combination like [A1,A2,B1,B2,B3],[A1,A1,B2,B3,B4] is legal. I.e. Repetition values in the table are allowed where as repetition of values in Table B is not allowed i.e. [A1,A2,B1,B1,B1] is not allowed. How can we write a SQL query for this? Thanking you all in advance.

Upvotes: 0

Views: 1345

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can get all the combinations by using a cross join. Your goal, then, is to filter these subject to the conditions on the "b" table. The following query does this with cross join and a where clause:

SELECT a1.value AS a1, 
       a2.value AS a2, 
       b1.value AS b1, 
       b2.value AS b2, 
       b3.value AS b3 
FROM   tablea a1 
       CROSS JOIN tablea a2 
       CROSS JOIN tableb b1 
       CROSS JOIN tableb b2 
       CROSS JOIN tableb b3 
WHERE  b1.value < b2.value 
       AND b2.value < b3.value;

To avoid repetition, the values in TableB are required to be in order.

Upvotes: 3

Related Questions