Welcome!

[MartinD]

Hi all
I have two tables:

Galleries (fields: id, name)
GalleryImages (fields: id, galleryId, imageSrc)

I want to make a list of all galleries, where I also select the imageSrc of ONE image to each gallery. I have tried some join statements, but no matter how I do it, it always returns a row for EACH picture in EACH gallery How do I make a correct SQL query for this operation, any ideas?

[Simon J.]

This should do it:

Code: Select all

SELECT `g`.`id`, `g`.`name`, (SELECT `imageSrc` FROM `GalleryImages` WHERE `galleryId` = `g`.`id` LIMIT 1) AS `image` FROM `Galleries` AS `g`

[MartinD]

Great That works

However, this is a statement in a statement. Does that affect performance (for the better or for the worse) compared to a join statement?

[Simon J.]

hmm actually I have no idea. This querry returnes the same result but using JOIN:

Code: Select all

SELECT `g`.`id`, `g`.`name`, `i`.`imageSrc` FROM `Galleries` AS `g` LEFT JOIN (`GalleryImages` AS `i`) ON ( `g`.`id` = `i`.`galleryId`) GROUP BY `g`.`name`

Do some testing, I think this is the best way to figure out wich one is faster.