-1

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         |
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jimangel2001
  • 13
  • 3
  • 2
    what did you try to solve this yourself? Stackoverflow is not a code writing service! – Luuk May 04 '22 at 16:51
  • 1
    see: [Best way to select random rows PostgreSQL](https://stackoverflow.com/questions/8674718/best-way-to-select-random-rows-postgresql) – Luuk May 04 '22 at 16:53
  • I have tried adding a new column on a cte based on table_1 with the amount of selections I will get from table_2. I will select randomly from table_2 with that limit, but I don't know how to do it in a single query for all rows of table1. @Luuk – jimangel2001 May 04 '22 at 17:09

1 Answers1

0
JOIN LATERAL

did the thing for me.

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