I have 3 tables:
A (id, b_id)
B (id, city)
C (message)
I need to build a query:
SELECT b.city, random(unique(c.message)) from A a, B b, C c where a.b_id = b.id;
Of course my query here is an example of what I need.
I need for every row of the join of A and B a unique message from C, randomized. We know for sure that there is enough messages to be uniquely randomized across the result of the join of A and B.
This may not be the most efficient way, but it would map a unique C.message to every (A, B) assuming count(C) >= count(A, B).
SET @i:=0;
SET @J:=0;
SELECT ab_mapping.city, c_index.message
FROM (SELECT B.city, @i as ind, @i:=@i+1
FROM A JOIN B
ON A.b_id = B.id) as ab_mapping
JOIN
(SELECT c_rand.message, @j as ind, @j:=@j+1
FROM (SELECT * FROM C ORDER BY RAND()) as c_rand) as c_index
ON ab_mapping.ind = c_index.ind;
This sub-query assigns a unique integer (0 <= i < count(A, B)) to (A, B) mapping:
SELECT B.city, @i as ind, @i:=@i+1
FROM A JOIN B
ON A.b_id = B.id;
This sub-query assigns a unique integer (0 <= j < count(C)) to every message (in random order) in C:
SELECT c_rand.message, @j as ind, @j:=@j+1
FROM (SELECT * FROM C ORDER BY RAND()) as c_rand;
The final query joins both sub-queries on ind to assign a unique random message to each (A, B).
I strongly suggest you use code for this.
You want the sql interpreter to walk two sets, which is fair enough, but your query will be hard to understand and therefore hard maintain. "Hard" has a consequence: expensive.
Likely you'll walk the result set anyway, right?
