Hello. There is a task to display each latest image for each club entry. I solved the problem this way, but displays the latest image from the entire database for each club entry. I understand that there is no connection by id , but I don’t know how to solve it.

 select db_club. *, db_images.name as iName
 from `db_club`,` db_images` 
 where db_club.type = '1' 
     and db_club.type = db_images.type 
     and db_images.id = (
         select max (db_images.id) 
         from db_images, db_club 
         where db_images.type = db_club.type and db_images.type = '1'
     ) 
 order by db_club.id desc

Tried it like that. In this case, the output is from the right club but the first picture. And we need the latter.

 select db_club. *, db_images.name as iName
 from `db_club`,` db_images` 
 where db_club.type = '1' 
     and db_club.type = db_images.type 
     and db_images.bdid = db_club.id
 group by db_images.bdid
 order by db_club.id desc

Db_club table structures enter image description here

Db_images table structures enter image description here

  • Add the group by db_images.type internal subquery of the first variant and remove the db_club table from there, it is not needed there - Mike
  • Try to remake the query using LEFT JOIN - asd
  • And it is not at all clear why you link tables across the type field. it would be logical if db_club.id participated there (and on it group by do for the maximum). But I don’t see in the lice structure whatever field the images would refer to it - Mike
  • And debug the requests in parts, take the internal sub-request, get what he would give the desired maximum values, then put it in the main one - Mike
  • @Mike bdid in images => id in club. type is needed to define the page, it is here as add. field and does not participate in the selection of the last picture. - Slavik Okara

1 answer 1

 select db_club.*, db_images.name as iName from `db_club`, `db_images` where db_club.type = 1 and db_club.type = db_images.type and db_images.bdid = db_club.id and db_images.id IN ( select max(id) from db_images where type = 1 group by bdid ) order by db_club.id desc

Only this option will not display clubs for which there is not a single picture, if this is not true, use LEFT JOIN instead of a comma.