CONCAT with Tags

I have the following query to select photos, their tags, and some basic user information:

SELECT photos.*, GROUP_CONCAT(tags.tag SEPARATOR ', ') AS photo_tags 
    FROM photos 
        INNER JOIN users 
            ON photos.upload_by = users.user_id
        INNER JOIN tags_photos 
            ON tags_photos.photo_id = photos.photo_id
        INNER JOIN tags 
            ON tags.tag_id = tags_photos.tag_id
    WHERE photos.status 开发者_开发知识库= '$status' 
        AND photos.photo_id IN (SELECT photos.photo_id 
                                    FROM photos
                                        JOIN tags_photos 
                                            ON photos.photo_id = tags_photos.photo_id
                                        JOIN tags 
                                            ON tags_photos.tag_id = tags.tag_id) 
    GROUP BY photos.photo_id 
    ORDER BY status, upload_date;

It works well, the only exception being that if the photo has no tags, it doesn't return that photo. I'd like it to still return the photo even if there are no tags.

Does anyone have any suggestions?

---

Not sure why you'd need the IN subquery in the WHERE clause.

SELECT photos.*, GROUP_CONCAT(tags.tag SEPARATOR ', ') AS photo_tags 
    FROM photos 
        INNER JOIN users 
            ON photos.upload_by = users.user_id
        LEFT JOIN tags_photos 
            INNER JOIN tags 
                ON tags.tag_id = tags_photos.tag_id
            ON tags_photos.photo_id = photos.photo_id
    WHERE photos.status = '$status' 
    GROUP BY photos.photo_id 
    ORDER BY status, upload_date;

---

Looks like you need a RIGHT OUTER JOIN between tags_photos and photos

SELECT photos.*, GROUP_CONCAT(tags.tag SEPARATOR ', ') AS photo_tags FROM photos 
    INNER JOIN users ON photos.upload_by = users.user_id
    RIGHT OUTER JOIN tags_photos ON tags_photos.photo_id = photos.photo_id
    INNER JOIN tags ON tags.tag_id = tags_photos.tag_id
    WHERE photos.status = '$status' AND photos.photo_id IN (
        SELECT photos.photo_id 
        FROM photos
        LEFT OUTER JOIN tags_photos ON photos.photo_id = tags_photos.photo_id
        JOIN tags ON tags_photos.tag_id = tags.tag_id
    ) 
    GROUP BY photos.photo_id ORDER BY status, upload_date;