MySQL: one to many

nunomira

hi,

Simple version:
I have a news table which has id and title.
I have a photos table which has id, id_news and importance.

News entries may or may not have photos.
News entries may have more than one photo.

How do I get all the news entries with along with the photo with highest importance?

I have tried

SELECT `news`.`id` AS id, `title`, `photo`
FROM (`news`)
LEFT JOIN `photos` ON `news`.`id` = `photos`.`id_news`
GROUP BY `news`.`id`
ORDER BY `date` DESC, `importance` DESC

but I can't seem to get the correct photo, the one with the highest importance.

nunomira

came up with this and it seems fine...

SELECT news.id AS id, title, photo
FROM news
LEFT JOIN (

SELECT p1.id_news AS id_news, p1.photo AS photo, p1.importance AS importance
FROM photos AS p1
INNER JOIN 
(
	SELECT id_news, MAX(importance) AS maxImportance
	FROM photos
	GROUP BY id_news
)
AS p2
ON p1.id_news = p2.id_news AND p1.importance = p2.maxImportance

)
AS photosImportance
ON news.id = photosImportance.id_news
WHERE active = 1
ORDER BY date DESC