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 |
Advertisement
Answer
JOIN LATERAL
did the thing for me.
SELECT * FROM TABLE1 LEFT JOIN LATERAL( SELECT * FROM TABLE2 LIMIT FLOOR(RANDOM() * 3 + 1)) a ON TRUE