Generate random pairs SQL-CodePudding

Suppose we have these two tables.

TABLE1:

|column_1 |    ...  |
--------------------
|   'a'   |    ...  |  
|   'b'   |    ...  |  
|   'c'   |    ...  |   
|   'd'   |    ...  |   
|   'e'   |    ...  |

TABLE_2:

|column_1 |    ...  |
--------------------
|   1     |    ...  |  
|   2     |    ...  |  
|   3     |    ...  |   
|   4     |    ...  |   
|   5     |    ...  |

I want to pair all rows of TABLE_1 with some random columns from TABLE_2 where each pair is gonna have a random amount of distinct rows from TABLE_2 (range 1,2,3)

An output could be:

|column_1 |    column_2  |
---------------------------
|   'a'   |    1         | 
|   'a'   |    2         | 
|   'a'   |    5         | 
|   'b'   |    5         | 
|   'c'   |    3         | 
|   'c'   |    4         | 
|   'd'   |    3         | 
|   'e'   |    3         | 
|   'e'   |    5         | 
|   'e'   |    1         |

CodePudding user response：

JOIN LATERAL

did the thing for me.

SELECT *
FROM TABLE1
LEFT JOIN LATERAL(
SELECT * 
FROM TABLE2 LIMIT FLOOR(RANDOM() * 3   1)) a
    ON TRUE