0

Each news can have several pictures, which are ordered by the 'o' field.

news

  • id_news
  • date_publish
  • title

pictures

  • id
  • id_news
  • filename
  • o (order in which the pictures should be presented: 1, 2, 3...)

This query returns a list of news titles, along with the first picture for the thumbnail.

SELECT n.title, p.filename
FROM news as n
LEFT JOIN picture AS p ON (p.id_news = n.id_news AND p.o = 1)
ORDER BY date_publish DESC

The problem is that, because of the CMS, if the admin deletes the picture with o=1, the o=2 should take place in the previous query. So the question is: how do i add the filename of the first available picture in the news titles query? (it should be the picture record that has the lowest value in the field o)

Andres SK
  • 10,779
  • 25
  • 90
  • 152
  • Use P as a subquery limiting results to top 1 based on the join to news order the result by o asc. – xQbert Mar 10 '14 at 15:38
  • I'm trying to avoid subqueries because they have significant impact when there are too many records. – Andres SK Mar 10 '14 at 15:39
  • possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Barmar Mar 10 '14 at 15:46
  • 1
    I think a subquery - or additional query - is the only way to do this. The subquery might look like `SELECT p.filename FROM p WHERE p.id_news = n.id_news ORDER BY p.o LIMIT 1` – erik258 Mar 10 '14 at 16:46

1 Answers1

1

How about adding a different join here?

SELECT n.title, p.filename
FROM news as n
LEFT JOIN
picture p on 
(p.id_news = n.id_news)
inner join
(select id_news,min(o) o from picture) AS p1
ON (p1.id_news = n.id_news AND p1.o = p.o)
ORDER BY n.date_publish DESC
KrazzyNefarious
  • 3,202
  • 3
  • 20
  • 32