I'm close - probably - but not quite there. Basically:
Three tables: Projects, ProjectCategories, and ProjectImages.
Projects have a category_id.
Images have a project_id.
Images also have an is_default binary field - if it's 1, the image is the default image for its project. Only one image per project can be default. However, a project may not necessarily have any images associated with it.
So... I want to display a list of categories. But I want each category to have an image displayed with it. Seeing as categories and images aren't directly related - only through projects - I'm trying to join images to projects only where the image is default.
It's not working
This is what I've got:
SELECT c.id, c.category, i.thumb_small
FROM ProjectCategories c
INNER JOIN Projects p ON p.category_id = c.id
LEFT JOIN Images i ON i.project_id = p.id AND i.is_default = 1
GROUP BY c.category
ORDER BY c.category
It's returning NULL for some categories. I can only assume that this is because the first project it's in those categories doesn't have any images, but I don't know what do do about it.