php - How to join two table and get the correct records

Question

I have one table containing pages/ad and another table with x pictures for each ad. The pictures table also have a “sortorder” column.

I’m trying to write some SQL, where i get the title from an ad and only one picture where sortorder is the lowest.

So far i’m doing this. But that just gave me a picture, but not the picture i want.

SELECT ads.id, ads.title, min(ads_gallery_files.filename) as picture, clients.name
FROM ads LEFT JOIN ads_gallery_files ON ads_gallery_files.ads_id=ads.id 
GROUP BY ads.id

I also have a client table, where i can see who created the ad. I’m using a left join to collect that name

LEFT JOIN clients ON clients.id=ads.client_id

Maybe i have to do a SELECT in a SELECT, don’t know.

Answer 1

You'll need a table expression (that I named m) to compute the minimum sortorder for each ad. Then you can just join this table to filter out the "extra" rows you don't want.

For example:

select
   a.id, a.title, f.filename as picture, c.name
from ads a
join ads_gallery_files f on f.ads_id = a.id
join ( -- this is called a "table expression" since it acts as a table
  select ads_id, min(sortorder) as minorder
  from ads_gallery_files f
  group by ads_id
) m on m.ads_id = f.ads_id and m.minorder = f.sortorder
left join clients c on c.id = a.client_id