Limit with a join

[mousematt]

Ok, I think this is going to be hard to explain so please keep with it!!

I have a small gallery section on my site, and would like to have a page with all the gallery names and an example picture from each gallery as links to the actual gallery.

All the gallery information is kept in a table called 'gallery' (i know, crazy naming convention!) and then all the picture details are kept in a table called 'files'.

Gallery Table
-------------
g_id | g_name


Files Table
-----------
f_id | f_g_id | f_file


SQL so far
------------
SELECT gallery. * , files . *
FROM gallery
LEFT JOIN files ON files.f_g_id = gallery.g_id


This brings back all of the files and obviously brings back the gallery names loads of times, once with each record from 'files'.

Is there and way of limiting this or some sort of distinct so that I get all the records from 'gallery' once with one record from 'files'

 

[r937]

which one row from files? latest? first? random?

note that depending on your answer, it could be really hard to do in mysql

rudy

http://r937.com/

SQL Consulting

 

[mousematt]

Don't care which row from files, just want an example from the the gallery, so random/any!