beans beans - 5 months ago 9
MySQL Question

Join One to Many - Display First Row from Many Table per Foreign Key

I want to get the first 5 rows from gallery using the following query, but I want to only get the first match from _gallery_gallery_items per row.

So for example, I want to get dog gallery, cat gallery and subsequent gallery titles from gallery but I only want to get the first match from _gallery_gallery_items where _gallery_gallery_items.gallery_id = gallery.id.

So the results should look like:

id = 1
title = dog gallery
gallery_item_id = 70

id = 2
title = cat gallery
gallery_item_id = 102

id = 3
title = mouse gallery
gallery_item_id = 500


Subsequent gallery_item_ids should not appear for the dog gallery, eg. 72, 72

Here is my query so far:

SELECT gallery.id, gallery.title, _gallery_gallery_items.gallery_item_id
FROM gallery LEFT JOIN
_gallery_gallery_items ON gallery.id = _gallery_gallery_items.gallery_id


And the tables:

gallery

id|title
1 |dog gallery
2 |cat gallery
3 |mouse gallery


_gallery_gallery_items

id|gallery_id|gallery_item_id
1| 1 | 70
2| 2 | 102
3| 1 | 71
4| 1 | 72
5| 3 | 500

Answer

I think you can achieve this using MAX() and GROUP BY aggreate functions

For the last gallery_item_id

SELECT a.id, a.title, MAX(b.gallery_item_id)
FROM gallery a
LEFT JOIN _gallery_gallery_items b 
ON a.id = b.gallery_id
GROUP BY b.gallery_id

For the first gallery_item_id

SELECT a.id, a.title, MIN(b.gallery_item_id)
FROM gallery a
LEFT JOIN _gallery_gallery_items b 
ON a.id = b.gallery_id
GROUP BY b.gallery_id, a.title