MySQL Join/Group_Concat Second Table

MySQL JOIN / GROUP_CONCAT second table?

It does weird things, becaue there is a cross product of certain rows. You can use the DISTINCT keyword to get only unique phone numbers:

GROUP_CONCAT(DISTINCT phones.phone_number) AS phone_numbers,

Check the documentation. Alternatively, you can get the phone numbers in another query where you would select only the phone numbers with a condition like WHERE phones.user_id IN (x, x, x, ...) (x are IDs returned from the first query).

mysql group_concat on multiple tables

The issue here is that there are two different junction tables (and two different connection chains), originating from a single table post. So a linear JOIN chain does not work. Duplicates in one of the junction table leads to duplication in other chain, when linear joining is done.

One way is to consider these two different JOIN chains in two separate Derived Tables (subqueries inside the FROM clause), and determine their respective grouped/aggregated expressions. We can then JOIN back these two chains using post_id.

Query

SELECT
  dt1.post_id, 
  dt1.flows, 
  dt1.flow_types, 
  dt2.powers, 
  dt2.power_types 
FROM 
(
  SELECT 
    p.post_id, 
    GROUP_CONCAT(t1.flow) AS flows, 
    GROUP_CONCAT(typ.type) AS flow_types
  FROM posts p
  LEFT JOIN posts_test1 pt1 
    ON pt1.post_id = p.post_id 
  LEFT JOIN test1 t1 
    ON t1.test1_id = pt1.test1_id 
  LEFT JOIN types typ 
    ON typ.type_id = pt1.type_id 
  GROUP BY p.post_id 
) AS dt1 
JOIN 
(
  SELECT 
    p.post_id, 
    GROUP_CONCAT(t2.power) AS powers, 
    GROUP_CONCAT(typ.type) AS power_types 
  FROM posts p
  LEFT JOIN posts_test2 pt2 
    ON pt2.post_id = p.post_id 
  LEFT JOIN test2 t2 
    ON t2.test2_id = pt2.test2_id 
  LEFT JOIN types typ 
    ON typ.type_id = pt2.type_id 
  GROUP BY p.post_id 
) AS dt2
  ON dt1.post_id = dt2.post_id;

Result

| post_id | flows       | flow_types | powers | power_types |
| ------- | ----------- | ---------- | ------ | ----------- |
| 1       | 100,140     | a,b        | 1,1    | a,b         |
| 2       | 200,200,200 | a,b,c      |        |             |

---

View on DB Fiddle

group_concat works incorrect on multiple join

The problem is when you have multiple matchs in both tables for a given entity: the joins multiply the rows, and the results of the aggregates are wrong.

I would recommend pre-aggregation. A couple of subqueries should do the job just fine:

select e.id, e.author, 
    (select group_concat(f.file_name) from file f where f.entity_id = e.id) as files_names
    (select group_concat(s.section_id) from section s where s.entity_id = e.id) as section_ids
from entity e

JOIN and GROUP_CONCAT with three tables

Its not particularly difficult.

1. Join the three tables using the JOIN clause.
2. Use Group_concat on the fields you're interested in.
3. Don't forget the GROUP BY clause on the fields you're not concatenating or weird things will happen

SELECT u.id, 
       u.Name, 
       Group_concat(us.id_sport order by pref) sport_ids, 
       Group_concat(s.name order by pref)      sport_names 
FROM   users u 
       LEFT JOIN User_Sports us 
               ON u.id = us.id_user 
       LEFT  JOIN sports s 
               ON US.id_sport = s.id 
GROUP  BY u.id, 
          u.Name 

DEMO

Update LEFT JOIN for when the user doesn't have entries in User_Sports as per comments

MySQL - GROUP_CONCAT() with joined table - unexpected results

The problem is the GROUP BY parentId, place the GROUP_CONCAT() in a subquery and then use a LEFT JOIN on the subquery. When the GROUP BY parentId is equal to null those values are ignored so you return no result:

SELECT  a.`storeID`, 
        a.`typeID`, 
        a.`cost`, 
        a.`quantity` , 
        `reqItems` 
FROM    lpStore a
LEFT JOIN
(
  select  parentID,
     GROUP_CONCAT( CONCAT( quantity,  " x ", typeID ) ) AS  `reqItems` 
  from lpRequiredItems
  group by parentID
) c 
  ON ( a.storeID = c.parentID ) 
ORDER   BY  a.`storeID`, 
        a.`typeID`, 
        a.`cost`, 
        a.`quantity`
LIMIT 0 , 30

See SQL Fiddle with Demo

The result is:

| STOREID | TYPEID | COST | QUANTITY |                    REQITEMS |
--------------------------------------------------------------------
|       1 |   2514 | 2000 |        3 |          5 x 3668,10 x 4825 |
|       2 |   3199 | 1000 |        1 |                      (null) |
|       3 |   8785 | 5000 |        2 | 5 x 9813,1 x 4875,15 x 1102 |
|       4 |    579 | 1500 |        5 |                      (null) |

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 LIKE from JOIN clause and GROUP_CONCAT returns only one row from joined table

The problem is you need the where to occur after the group concat This is one way using a sub select

Select * from (
    SELECT re.id, GROUP_CONCAT( r.name ) AS address
    FROM realestate re 
    JOIN realestate_regions rr 
      ON re.id = rr.reid 
    LEFT JOIN regions r 
      ON rr.rid = r.id 
    GROUP BY re.id) b
    WHERE (Address LIKE '%san%') 

Another... and more standard would be to use the having which applies after the aggregate is calculated.

    SELECT re.id, GROUP_CONCAT( r.name ) AS address
    FROM realestate re 
    JOIN realestate_regions rr 
      ON re.id = rr.reid 
    LEFT JOIN regions r 
      ON rr.rid = r.id 
    GROUP BY re.id 
    Having address like '%san%' 

I still can't attest that the order of the group_concat will be consistent when multiple records are encountered.

GROUP_CONCAT on two different tables

You can construct the final result like this:

SELECT s.name,
       CONCAT('[',
              GROUP_CONCAT(DISTINCT '{''id'':''', ar.role_id, ''',''role_name'':''', r.role_name, '''}'),
              ']')
FROM staff s LEFT JOIN
     assigned_roles ar
     ON ar.staff_id = s.id LEFT JOIN
     roles r
     ON r.id = ar.role_id
GROUP BY s.id;

There might be a typo with all those single quotes and unusual characters.

---

Related Topics