CODEWITHSUNDEEP
mysqlsqlrandom
TheGPWorx
TheGPWorx

Reputation: 887

How to randomize the sql result set from a two tables?

I have two tables

Table1 person:
-person_id
-name

Table2 expertise:
-expertise_id
-person_id
-expertise_name

What I want to achieve is to return 5 random persons with a corresponding 2 random expertise of that person. So far I can return all the persons and all their expertise.

SELECT * FROM person p, expertise e WHERE e.person_id = p.person_id

Can anyone help me on this? Thank you in advance.

Upvotes: 1

Views: 19

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Probably the easiest way is to return the expertises in a single row:

select p.*,
       substring_index(group_concat(e.expertise_name order by rand()), ',', 2) as two_expertises
from (select p.*
      from person p
      order by rand()
      limit 5
     ) p left join
     expertise e
     on p.person_id = e.person_id
group by p.person_id;

Upvotes: 1

Related Questions