Pairing a max number of rows from a table to another-CodePudding

Suppose we have two tables where T1 has less rows than T2 and we want to pair T1 rows with a maximum of 3 distinct random T2 rows. Every T1 row must be paired while not all T2 rows need to be paired

T1:
|c1  | ...|
------------
|'a' | ...|  
|'b' | ...|  
|'c' | ...|   


T2:
|c1 |...|
----------
|1  |...|  
|2  |...|  
|3  |...|   
|4  |...|   
|5  |...| 
|6  |...| 


|c1 |c2 |
----------
|'a' | 3| 
|'a' | 4| 
|'a' | 1| 
|'b' | 6| 
|'c' | 2| 
|'c' | 5|

I have tried to give the T2 table a random number from 1 to the rows of T1 with the intention of joining the two tables based on the random number column of T2 and the row number of T1 but the problem is that i couldn't figure out how to restrict the amount of times that each random number appears to 3. So the question is what is the most efficient way to pair these tables?

CodePudding user response：

I doubt the usefulness of the exercise, and it won't be fast for large tables, but you could

SELECT a1.col1, a2.col2
FROM (SELECT row_number() OVER () AS n,
             c1 AS col2
      FROM t2) AS a2
   RIGHT JOIN (SELECT row_number() OVER (ORDER BY random()) AS n,
                     c1 AS col1
              FROM (SELECT c1 FROM t1
                    UNION ALL
                    SELECT c1 FROM t1
                    UNION ALL
                    SELECT c1 FROM t1) AS q
             ) AS a1
      USING (n);