Pairing a max number of rows from a table to another

Question

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?

Please provide enough code so others can better understand or reproduce the problem. — Community, May 05 '22 at 19:28
I think the solution proposed here suits your case better: https://stackoverflow.com/questions/72116650/generate-random-pairs-sql — jimangel2001, May 10 '22 at 14:50

Laurenz Albe · Accepted Answer · 2022-05-06T05:44:14.150

0

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);

edited May 06 '22 at 05:44

answered May 05 '22 at 14:28

Laurenz Albe

209,280
17
206
263

Thanks for the answer but you seem to be matching every single row of T2 to T1 with some rows of T1 being left out I want the opposite, every row of T1 being paired with T2 I don't mind if some T2 rows are left out. – kzaf May 05 '22 at 15:53
Don't be lazy, use a right outer join. – Laurenz Albe May 06 '22 at 05:43

Pairing a max number of rows from a table to another

1 Answers1