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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

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

Post 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!
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

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

Post 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'
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

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

Post 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
Post Reply