[SOLVED] Mysql simple Join & ordering

[avvllvva]

I hav two tables

tbl_gallery (id,title) & tbl_gallery_images (id,gallery_id,image)

 

I want to display latest 6 images from each gallery. ie; choosing latest 6 records from tbl_gallery and getting  single corresponding latest image from tbl_gallery_images.

 

something like this , but not actually

select a.title,b.image from tbl_gallery a join tbl_gallery_images b where a.id=b.gallery_id order by a.id desc, b.id desc limit 0,6;

 

but this will display latest images from the table regardless of distinct gallery.

 

I want only one latest image from the 6 latest gallery.

[kickstart]

Hi

 

Think this is what you want.

 

SELECT a.id, a.title, b.image 
FROM tbl_gallery a
INNER JOIN (SELECT gallery_id, MAX(id) AS MaxId FROM tbl_gallery_images) z
ON a.id = b.gallery_id
INNER JOIN tbl_gallery_images b
ON z.MaxId = b.id
ORDER BY a.id DESC
LIMIT 6

 

All the best

 

Keith

[avvllvva]

Yes. This is what I really want.

 

but there was some errors on your query, I have fixed that.. this is the modified one

======================================

SELECT a.id, a.title, b.image

FROM tbl_gallery a

INNER JOIN (SELECT gallery_id, MAX(id) AS MaxId FROM tbl_gallery_images group by gallery_id) z

ON a.id = z.gallery_id

INNER JOIN tbl_gallery_images b

ON z.MaxId = b.id

ORDER BY a.id DESC

LIMIT 6

==============================================

Now Its working,

Thank you very much.