Group-concat problems, the result is wrong

Question

I have this query that works correctly (tested on phpmyadmin):

SELECT articoli.id AS id_articoli,
              articoli.titolo,
              articoli.descrizione,
              galleria.id AS id_galleria,
              group_concat(galleria.foto) as immagini
              FROM articoli
              LEFT JOIN galleria
              ON articoli.id = galleria.rif_id
              WHERE articoli.genere1 = 1       
              AND articoli.visibile = 1
              GROUP BY articoli.id
              ORDER BY articoli.id ASC

With this mysql query I am able to fetch all item that belong to a category and for every item i can fetch all the respective images.

Now I am trying to update/upgrade this query and trying to fetch also the videos (if any) for the item (this for every item that belong to a category).

I was able to upgrade the query as below but it gives me a strange result and obviously I am doing the query in the wrong way.. but I don't know what I am doing wrong..

SELECT articoli.id AS id_articoli,
              articoli.titolo,
              articoli.descrizione,
              galleria.id AS id_galleria,
              group_concat(galleria.foto) as immagini,
              group_concat(videos.video) as video
              FROM articoli
              LEFT JOIN galleria
              ON articoli.id = galleria.rif_id
              LEFT JOIN videos
              ON articoli.id = videos.rif_id  
              WHERE articoli.genere1 = 1       
              AND articoli.visibile = 1                  
              GROUP BY articoli.id
              ORDER BY articoli.id ASC

This query returns me (if some video exists)the same number of video and images... If the item has 3 images it returns also 3 videos and that's not true because some item could have 3 images and 1 video or viceversa.. Can you help me to fix the query?

Answer 1

The simplest solution is DISTINCT:

SELECT a.id AS id_articoli,
       a.titolo,
       a.descrizione,
       group_concat(distinct g.foto) as immagini,
       group_concat(distinct v.video) as video
FROM articoli a LEFT JOIN
     galleria g
     ON a.id = g.rif_id LEFT JOIN
     videos v
     ON a.id = v.rif_id  
WHERE a.genere1 = 1 AND     
      a.visibile = 1                  
GROUP BY a.id
ORDER BY a.id ASC;

Notes:

• I added table aliases. This makes the query easier to write and to read.
• The distinct keyword should remove the duplicates in the two lists.
• I removed g.id AS id_galleria. This is not appropriate, because there are multiple matches. You can use an aggregation function on g.id (such as MIN(), MAX(), or GROUP_CONCAT()).
• The GROUP BY a.id is fine, assuming the id is the primary key (or unique) in the table and the only unaggregated columns come from that table.

Also, the DISTINCT works if you have a handful of images and galleries. If you have larger amounts of data, you might need another strategy, such as aggregating before joining.