CODEWITHSUNDEEP
mysqlsqlrandom
Mohammad Qasim
Mohammad Qasim

Reputation: 186

How to join tables randomly in MySQL?

I have two tables (TableA and TableB)
TableA has 10 rows and TableB have 50 rows.
I want each row in TableA to be joined with X number of rows from TableB randomly.

For example if TableA has rows (a,b,c) and TableB have rows (p,q,r,s,t,u) and X=2 ,then expected result is:

a-p
a-u
b-r
b-t
c-s
c-q

This joining should be random.

Upvotes: 1

Views: 154

Answers (1)

rtruszk
rtruszk

Reputation: 3922

You can use RAND() :

select x,y from 
(
    select t.*,  @rownum := @rownum + 1 AS rank from
    (
        select tablea.x, tableb.y, rand() r
        from tablea cross join tableb
         order by x, r
    ) t, (SELECT @rownum := -1) r
) tr
where tr.rank%(select count(y) from tableB)<2

In above query I am joining every element from tableA with every element of tableB. For every such row RAND() value is computed. Then such table is sorted by this rank for every value from tableA. Then I only need select first X rows for every value.

Number 2 in this query is your X.

You can see it in SQL Fiddle

EDIT:

I have found a solution where tableB elements are not repeated:

select ta.x, tb.y from 
(
   select y, @rownumB := @rownumB + 1 as rnB 
   from  
   (select y, rand() as r from tableB order by r) t, (SELECT @rownumB := -1) rownB
) tb,
(
   select x, @rownumA := @rownumA + 1 as rnA
   from tableA, (SELECT @rownumA := -1) rownA
) ta
where tb.rnB%5=ta.rnA
order by ta.x;

Above query does:

  • shuffles tableB
  • assigns row numbers rnB (0..14) to rows of shuffled tableB
  • assigns row numbers rnA (0..4) to rows of tableA
  • joins 'tableB' with tableA using % operator
  • tableB has 15 elements. So rnB%5 will give numbers (0, 1, 2, 3, 4, 0, 1, 2, 3, 4, 0, 1, 2, 3, 4). These numbers are joined with rnA. So every row from tableA is joined with 3 rows of tableB

See this in SQLFiddle

Upvotes: 1

Related Questions