带有标签的 CONCAT

发布于 2024-11-28 10:55:01 字数 936 浏览 0 评论 0原文

我有以下查询来选择照片、其标签和一些基本用户信息：

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;

它运行良好，唯一的例外是，如果照片没有标签，则不会返回该照片。即使没有标签，我希望它仍然返回照片。

有人有什么建议吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我的痛♀有谁懂 2024-12-05 10:55:02

不确定为什么需要在 WHERE 子句中使用 IN 子查询。

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;

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;

回复收藏 0 原文

死开点丶别碍眼 2024-12-05 10:55:02

看起来您需要在 tags_photos 和 photos 之间进行 RIGHT OUTER JOIN

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;

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;

回复收藏 0 原文

~没有更多了~