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:
Code:
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.
Any suggestions?
dBforums
>
Joining on non-null field?
12-08-08, 06:48
Linear Mode
