MySQL random join

Question

Here is my MySQL database:

enter image description here

And my query to get all subcategories:

SELECT `a`.`id`, `a`.`name`, `a`.`url_segment`, `a`.`categories_id`, `b`.`image` AS supplements_image
FROM (`subcategories` AS a)
LEFT JOIN `supplements` AS b ON `b`.`subcategories_id` = `a`.`id`
LEFT JOIN `reviews` AS c ON `c`.`supplements_id` = `b`.`id`
GROUP BY `a`.`id`
ORDER BY `a`.`categories_id` ASC, COUNT(c.id) DESC, `b`.`image` ASC

The problem is with the subcategory image, that should be the image of a random product inside that subcategory, but I'm always getting the image of the first product. Any idea how I can do this?

Francis P · Accepted Answer · 2012-11-20 18:16:04Z

4

SELECT `a`.`id`, `a`.`name`, `a`.`url_segment`, `a`.`categories_id`, 
        (SELECT `image` 
         FROM `supplements` 
         WHERE `subcategories_id` = `a`.`id` 
         ORDER BY RAND() LIMIT 1
        ) AS supplements_image
FROM (`subcategories` AS a)
LEFT JOIN `reviews` AS c ON `c`.`supplements_id` = `a`.`id`
GROUP BY `a`.`id`
ORDER BY `a`.`categories_id` ASC, COUNT(c.id) DESC

Edited unspecified alias b and now works great: See fiddle!

edited Nov 20, 2012 at 18:16

Francis P

13.7k3 gold badges30 silver badges52 bronze badges

answered Nov 20, 2012 at 17:56

Tom

6,6633 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL random join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related