如何将GROUP_CONCAT添加到LEFT JOIN查询？ [英] How to add GROUP_CONCAT to LEFT JOIN query?

问题描述

我有这个查询（和结果）：

I have this query (and results):

select articles.article_id, articles.article_text, article_photos.photo_filename
from
  articles
left join article_photos
on article_photos.article_id=articles.article_id

>>> results
1,some_text,photo1.jpg
1,some_text,photo2.jpg
1,some_text,photo3.jpg

如何将GROUP_CONCAT合并到此以便我得到：

How do I incorporate GROUP_CONCAT to this so that I get:

>>> results

1,some_text,photo1.jpg
NULL,NULL,photo2.jpg
NULL,NULL,photo3.jpg

基本上，我有一张带有文章的表格，以及带有图片的相关表格。一篇文章可能有多个图像属于它，所以我试图在while循环内的屏幕上打印它，并且不希望文本在有多个图像时反复重复。

Basically, I have a table with articles, and related table with images. An article can have multiple images belonging to it, so I'm trying to print it on screen within while loop, and don't want text to repeat over and over when there's multiple images.

推荐答案

select articles.article_id, articles.article_text, group_concat(article_photos.photo_filename)
    from articles
        left join article_photos
            on article_photos.article_id=articles.article_id
    group by articles.article_id, articles.article_text

会返回

would return

1    some_text    photo1.jpg,photo2.jpg,photo3.jpg

这与您在预期结果中显示的不一样。这是你要求的吗？

which is not quite what you've shown in your expected results. Is this what you're asking for?

这篇关于如何将GROUP_CONCAT添加到LEFT JOIN查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！