MYSQL Select 5 records from one table based on several different values from another

Question

I am by no means fluent in MYSQL. What I am trying to do is I have a table that has a list of Galleries with unique ID numbers. I have another table ImageData that has images that may be as many as 1000 that are related to each GalleryID that is in the first table. I want to do a select that will get 5 random records from each GalleryID. I am trying to do this without doing a loop and multiple hits to the database.

The tables are as follows Galleries -> GalleryID -> GalleryName

ImageData -> ImageID -> ImageName -> GalleryID

I hope this makes sense.

Answer 1

Hi Randy welcome to the club. Here is a simple and fast solution to your scenario. Enjoy

select g.GalleryName, i.imageName from Galleries g, imageData i, (SELECT gl.Galleryid from Galleries gl ORDER BY RAND() LIMIT 5) m where m.galleryID = i.galleryID