beans beans - 1 year ago 56
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 =

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.title, _gallery_gallery_items.gallery_item_id
_gallery_gallery_items ON = _gallery_gallery_items.gallery_id

And the tables:


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


1| 1 | 70
2| 2 | 102
3| 1 | 71
4| 1 | 72
5| 3 | 500

Answer Source

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

For the last gallery_item_id

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

For the first gallery_item_id

SELECT, a.title, MIN(b.gallery_item_id)
FROM gallery a
LEFT JOIN _gallery_gallery_items b 
ON = b.gallery_id
GROUP BY b.gallery_id, a.title
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download