Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Joining on non-null field?

    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?

  2. #2
    Join Date
    Jan 2002
    Posts
    189
    Oh, you fool. It's inner join, not left join.

    Sorry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •