Mysql: Group_Concat with Left Join

MySQL: GROUP_CONCAT with LEFT JOIN

Use:

   SELECT t.*,
          x.combinedsolutions
     FROM TICKETS t
LEFT JOIN (SELECT s.ticket_id,
                  GROUP_CONCAT(s.soution) AS combinedsolutions
             FROM SOLUTIONS s 
         GROUP BY s.ticket_id) x ON x.ticket_id = t.ticket_id

Alternate:

   SELECT t.*,
          (SELECT GROUP_CONCAT(s.soution)
             FROM SOLUTIONS s 
            WHERE s.ticket_id = t.ticket_id) AS combinedsolutions
     FROM TICKETS t

mysql left join with group_concat - only shows a single result

You are describing a left join:

select 
    a.accountID, 
    a.accountOpenedDate, 
    a.accountStatus, 
    group_concat(e.expenseType separator ':') as expense 
from account a
left join expense e on e.accountID = a.accountID
group by a.accountID
order by a.accountID

I also don't see the point for expression MONTH(DATE(AccountOpenedDate)) in the GROUP BY clause: you seem to want one row per account, so this seems irrelevant.

The above query works under the assumption that accountID is the primary key of table account: other columns from the same column are functionaly dependent on the primary key, so you do not need to list them in the group by clause. You could also write this as:

select 
    a.accountID, 
    a.accountOpenedDate, 
    a.accountStatus, 
    group_concat(e.expenseType separator ':') as expense 
from account a
left join expense e on e.accountID = a.accountID
group by a.accountID, a.accountOpenedDate, a.accountStatus
order by a.accountID

Side notes:

• table aliases make the query easier to write and read

• in a multi-table query, all columns should be qualified (prefixed) with the (alias of the) table they belong to

Using group_concat in left join

You need group by in the subquery:

SELECT *
FROM wp_fishy_posts p LEFT JOIN
     (SELECT GROUP_CONCAT(i.path,',') AS linked_images, l.postID as link_id 
      FROM wp_fishy_images i LEFT JOIN
           wp_fishy_images_post_link l
           ON l.imageID = i.ID
      GROUP BY l.postId
------^
     ) l
     ON l.link_id = p.ID
WHERE p.post_type = 'image';

GROUP_CONCAT with multiple LEFT JOINs

Try to split the query into the two and then perform the join

SELECT c.*, t2.modulepath, t1.subassemblypath 
FROM connectors c 
JOIN (
  SELECT 
   s.cid,
   GROUP_CONCAT(s.subassemblypath SEPARATOR "|") AS subassemblypath 
  FROM subassemblies s
  GROUP BY s.cid
) t1 ON c.id = t1.cid
JOIN
(
  SELECT 
   m.cid,
   GROUP_CONCAT(m.modulepath SEPARATOR "|") AS modulepath
  FROM modules m
  GROUP BY m.cid
) t2 ON c.id = t2.cid

MYSQL CONCAT + GROUP_CONCAT + LEFT OUTER JOIN

With a MySQL version prior to 5.7.22 you can't use the JSON built-in functions.

You'll have to use a few imbricated GROUP_CONCAT subqueries to obtain your JSON string.

As told in the comments, your expected JSON string is not valid. The following answer will differ from your expected result, to fix this issue.

I suggest you proceed with a first query to get a column with the "aeroport" names, and another column with the associated stations formatted as a list, for each couple of "rule.id + aeroport_name".

This gives the following query:

mysql> select rules.id, name, concat ('"', aeroport, '":') as aeroport_name, group_concat('"', station, '"') as station_list
    -> from rules
    -> inner join pivot on rules.id = pivot.id_rule
    -> inner join transferts on pivot.id_transfert = transferts.id
    -> group by rules.id, aeroport_name;
+------+------+---------------+---------------------+
| id   | name | aeroport_name | station_list        |
+------+------+---------------+---------------------+
|    1 | a    | "GNB":        | "La Tania"          |
|    1 | a    | "GVA":        | "Flaine"            |
|    2 | b    | "GNB":        | "La Tania","Flaine" |
|    2 | b    | "GVA":        | "Flaine"            |
+------+------+---------------+---------------------+
4 rows in set (0,00 sec)

Then, we are going to use this query as a subquery to associate each "station_list" to its given aeroport, in a rule id context, within a single string.

This give the following encapsulation:

mysql> select id, name, group_concat(aeroport_name, '[', station_list, ']') as aeroport_list
    -> from (
    ->    select rules.id, name, concat ('"', aeroport, '":') as aeroport_name, group_concat('"', station, '"') as station_list
    ->    from rules
    ->    inner join pivot on rules.id = pivot.id_rule
    ->    inner join transferts on pivot.id_transfert = transferts.id
    ->    group by rules.id, aeroport_name
    -> ) as isolated group by id;
+------+------+----------------------------------------------+
| id   | name | aeroport_list                                |
+------+------+----------------------------------------------+
|    1 | a    | "GNB":["La Tania"],"GVA":["Flaine"]          |
|    2 | b    | "GNB":["La Tania","Flaine"],"GVA":["Flaine"] |
+------+------+----------------------------------------------+
2 rows in set (0,00 sec)

And finally, we can now add the final "{}" encapsulation to our string, by adding a top level query over this:

mysql> select id, name, concat('{', aeroport_list, '}') as conf                                                                                                                                                                                   
    -> from (
    ->    select id, name, group_concat(aeroport_name, '[', station_list, ']') as aeroport_list
    ->    from (
    ->       select rules.id, name, concat ('"', aeroport, '":') as aeroport_name, group_concat('"', station, '"') as station_list
    ->       from rules
    ->       inner join pivot on rules.id = pivot.id_rule
    ->       inner join transferts on pivot.id_transfert = transferts.id
    ->       group by rules.id, aeroport_name
    ->    ) as isolated group by id
    -> ) as full_list;
+------+------+------------------------------------------------+
| id   | name | conf                                           |
+------+------+------------------------------------------------+
|    1 | a    | {"GNB":["La Tania"],"GVA":["Flaine"]}          |
|    2 | b    | {"GNB":["Flaine","La Tania"],"GVA":["Flaine"]} |
+------+------+------------------------------------------------+
2 rows in set (0,01 sec)

GROUP CONCAT and LEFT OUTER JOIN to split

You need two levels of aggregation: one by building_id and another by site_id.

with b_l as (
  select
    b.id as building_id
    , b.site_id
    , concat('('
      , group_concat(
          l.id
          order by l.id
          separator ','
        )
      , ')'
    ) as building_levels
  from buildings as b
    left join levels as l
      on b.id = l.building_id
  group by b.id, b.site_id
)
, s_b as (
  select
    s.id
    , group_concat(
        b_l.building_id
        order by b_l.building_id
        separator ','
      ) as buildings
    , group_concat(
        b_l.building_levels
        order by b_l.building_id
        separator ','
      ) as levels
  from sites as s
    left join b_l
      on s.id = b_l.site_id
  group by s.id
)
select *
from s_b

id | buildings | levels                 
-: | :-------- | :----------------------
 1 | 1,2,3     | (1,2),(3,4,5),(6)      
 2 | 4,5,6     | (7,8),(9),(10,11)      
 3 | 7,8,9     | (12,13,14),(15,16),(17)
 4 | 10        | null                   

Or the same with lateral join:

select
  s.id
  , group_concat(
      b_l.building_id
      order by b_l.building_id
      separator ','
    ) as buildings
  , group_concat(
      b_l.building_levels
      order by b_l.building_id
      separator ','
    ) as levels
from sites as s
  left join lateral (
    select
      b.site_id
      , b.id as building_id
      , concat('('
          , group_concat(
              l.id
              order by l.id
              separator ','
            )
          , ')'
        ) as building_levels
    from buildings as b
      left join levels as l
        on b.id = l.building_id
    group by b.id, b.site_id
  ) b_l
    on s.id = b_l.site_id
group by s.id

db<>fiddle here

How can I change a group_concat column that's left joined with another table to empty string instead of null when there are no matching records?

Wrap the result with IFNULL will do.

SELECT u.*, IFNULL(GROUP_CONCAT(pps.photo_key ORDER BY pps.is_primary DESC), '') as photo_keys
FROM users u 
LEFT JOIN profile_images pps ON u.id=pps.user_id
WHERE u.id = "555555555"
GROUP BY u.id`

GROUP_CONCAT with LEFT JOIN condition?

Storing comma separated ids is not a good idea see Database Normalization,you cannot simply use IN() to join with your set column for now you need to use FIND_IN_SET to find the values in a comma separated set

SELECT 
  GROUP_CONCAT(pr.id ORDER BY pr.id) AS promo_ids,
  p.id AS product_id,
  p.name AS product_name 
FROM
  `products` AS p 
  LEFT JOIN `promos` AS pr 
    ON FIND_IN_SET(`p`.`id`,pr.product_ids) 
WHERE p.category_id IN (1, 2, 3, 4) 
GROUP BY p.id 

Mysql group_concat(id) as ids in a left join and using ids to select all columns in id group

The simple fix would appear to be to use FIND_IN_SET for the join. But this is a bit of a hack and will not be that quick.

SELECT * 
FROM `view_test` 
INNER JOIN 
(
    SELECT GROUP_CONCAT(DISTINCT entity_id) AS idsgroup 
    FROM `view_test` 
    WHERE (attribute_name = 'sku') 
    GROUP BY value_name 
    LIMIT 0, 3 
) bind
ON FIND_IN_SET(entity_id, bind.idsgroup);

Further not sure why you have a LIMIT on the sub query, especially without an order clause.

Possibly better to use a sub query to just get the DISTINCT entity_id with an attribute_name of sku and join against that.

SELECT * 
FROM `view_test` 
INNER JOIN 
(
    SELECT DISTINCT entity_id 
    FROM `view_test` 
    WHERE (attribute_name = 'sku') 
) bind
ON view_test.entity_id = bind.entity_id

---

Related Topics