Two LEFT JOINs to join 2 tables + RAND()... ugh...
Posted: Sat Oct 31, 2009 5:17 pm
Query so far (this works wonderfully):
However, I want to limit this per school. `best` does not contain `school_id`, but `events` does and `best` does contain `event_id`.
What I want to do, but can't figure out:
I've tried many variations of this but can't get it to work.
Help!
Code: Select all
SELECT `best`.* FROM (SELECT `id` FROM `best` ORDER BY RAND() LIMIT 10) AS `random_best` LEFT JOIN `best` ON `best`.`id` = `random_best`.`id`;Code: Select all
best = id, event_id, image
events = id, school_id, ...Code: Select all
SELECT `best`.*,`events`.`id`,`events`.`school_id` FROM (SELECT `id` FROM `best` ORDER BY RAND() LIMIT 10) AS `random_best`, `events` LEFT JOIN `best` ON `best`.`id` = `random_best`.`id` LEFT JOIN `best` ON `best`.`event_id` = `events`.`id` WHERE `events`.`school_id`='$school_id';
#OR more clearly:
SELECT `best`.*,
`events`.`id`,`events`.`school_id`
FROM (SELECT `id` FROM `best` ORDER BY RAND() LIMIT 10) AS `random_best`,
`events`
LEFT JOIN `best` ON `best`.`id` = `random_best`.`id`
LEFT JOIN `best` ON `best`.`event_id` = `events`.`id`
WHERE `events`.`school_id`='$school_id';Help!