Very complex mysql query - random order on two tables

Question

Consider this classic setup:

entrytable:
id (int, PK)
title (varchar 255)

entry_categorytable:
entry_id (int)
category_id (int)

categorytable:
id (int, PK)
title (varchar 255)

This basically means that entries can be in one or more categories (the table is entry_categoryused as the MM / join table)

Now I need to request 6 unique categories along with 1 unique record from these categories using RANDOM!

EDIT: To clarify: the goal is to display 6 random categories with 1 random entry for each category .

The correct result set would look like this:

category_id   entry_id     
10            200 
20            300
30            400
40            500
50            600
60            700

This is not true as category_idthere are duplicates in the column :

category_id  entry_id
10           300
20           300
...

, member_id :

category_id  entry_id     
20           300
20           400
...

---

?

rand, :

select c.id, e.id
from category c    
inner join entry_category ec on ec.category_id = c.id
inner join entry e on e.id = ec.entry_id
group by c.id
order by rand()

, , , .

: , select distinct ... group by. distinct, , c.id e.id .

---

EDIT: , , , , , :

select t1.e_id, t2.c_id
from (select e.id as e_id from entry e order by rand()) t1
inner join (select ec.entry_id as e_id, ec.category_id as c_id from entry_category ec group by e_id order by rand()) t2 on t2.e_id = t1.e_id
group by t2.c_id
order by rand()

Answer 1

SELECT  category_id, entity_id
FROM    (
        SELECT  category_id,
                @ce :=
                (
                SELECT  entity_id
                FROM    category_entity cei
                WHERE   cei.category_id = ced.category_id
                        AND NOT FIND_IN_SET(entity_id, @r)
                ORDER BY
                        RAND()
                LIMIT 1
                ) AS entity_id,
                (
                SELECT  @r := CAST(CONCAT_WS(',', @r, @ce) AS CHAR)
                )
        FROM    (
                SELECT  @r := ''
                ) vars,
                (
                SELECT  DISTINCT category_id
                FROM    category_entity
                ORDER BY
                        RAND()
                LIMIT 15
                ) ced
       ) q
WHERE  entity_id IS NOT NULL
LIMIT  6

, , MySQL . .

, 6 ( entity_id ). 15 .

PRIMARY KEY category_entity (category_id, entity_id), .

Answer 2

, - 6 , ( ), .

, , .