Page 1 of 1

Two LEFT JOINs to join 2 tables + RAND()... ugh...

Posted: Sat Oct 31, 2009 5:17 pm
by Skara
Query so far (this works wonderfully):

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`;
However, I want to limit this per school. `best` does not contain `school_id`, but `events` does and `best` does contain `event_id`.

Code: Select all

best = id, event_id, image
events = id, school_id, ...
What I want to do, but can't figure out:

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';
I've tried many variations of this but can't get it to work.
Help!

Re: Two LEFT JOINs to join 2 tables + RAND()... ugh...

Posted: Sat Oct 31, 2009 10:52 pm
by Skara
bump
This I think is closer, actually, but it doesn't work either:

Code: Select all

SELECT `best`.*
    FROM (SELECT `best`.`id`,`best`.`event_id`, `events`.`id`, `events`.`school_id`
          FROM `best`,`events` JOIN `best` ON `best`.`event_id`=`events`.`id`
          WHERE `events`.`school_id` = '$school_id'
          ORDER BY RAND() LIMIT 10) AS `random_best`
    JOIN `best` ON `best`.`id` = `random_best`.`id`;
mysql_error() wrote:Not unique table/alias: 'best'

Re: Two LEFT JOINs to join 2 tables + RAND()... ugh...

Posted: Sun Nov 01, 2009 4:50 am
by VladSun
Why do you need a subselect?
[sql]SELECT     `best`.`id`,    `best`.`event_id`,     `events`.`id`,     `events`.`school_id`FROM     `best`,INNER JOIN    `events` ON `best`.`event_id`=`events`.`id`WHERE     `events`.`school_id` = '$school_id'ORDER BY     RAND() LIMIT     10[/sql]

Re: Two LEFT JOINs to join 2 tables + RAND()... ugh...

Posted: Sun Nov 01, 2009 3:57 pm
by Skara
Well, that works. Thank you!

However, the reason I was using a subselect is because I read this:
http://www.paperplanes.de/2008/4/24/mys ... _rand.html