SQL GROUP_CONCAT without GROUP BY?

Question

I have 4 tables

Main table holding the licence data called licence, for sake of example we care for 2 columns licence_id and owner_id

+------------+----------------+ 
| licence_id | owner_id       |
+------------+----------------+ 
| 1          |            124 |

Second table connecting the licence to countries licence_countries

+------------+----------------+ 
| licence_id | country_id     |
+------------+----------------+ 
| 1          |            45  |

Third table holding which countries belong to what region region_countries

+------------+----------------+ 
| region_id  |   country_id   |
+------------+----------------+ 
| 45         |            10  |
| 45         |            12  |

Fourth table holds title and description in different languages for the licence

+------------+----------------+----------+---------------+
| licence_id  |   language_id |   title  | description   |
+------------+----------------+----------+---------------+
| 10         |            18  | Licence  | Licence for.. |
| 10         |            13  | Licenz   | Licenz fur..  |

I am trying to generate the queries as such that the region and countries are comma separated per licence id. So 1 row would have licence id and ; separated list of regions in one column and the same in another column for countries. I am doing this with GROUP_CONCAT()

SELECT
    l.id,
    GROUP_CONCAT(rc.id_country SEPARATOR ';') AS countries,
    GROUP_CONCAT(rc.id_region SEPARATOR  ';') AS regions
FROM license l
LEFT JOIN licence_country lc ON l.id = lc.id_licence
LEFT JOIN region_country rc ON lc.id_country = rc.id_country
GROUP BY l.id

This works fine, but my problem is connecting the languages. The titles and descriptions we care about to not be joined in one row so for them we want the normal LEFT JOIN action where if we have 1 licence_id and 10 languages connected to it then we have 10 rows with duplicated values for language, countries, regions etc. For this I added the normal LEFT JOIN

LEFT JOIN license_language ll ON l.id = ll.id_licence

And it fails, because it wants the title and the id in the GROUP BY, but then it removes the duplicate entries.

I have duplicates for every licenceId and languageId since there are many combinations.

The end result should look like something like this

+------------+----------------+----------+---------------+--------------+----------+
| licence_id |   language_id  |   title  | description   | countries    | regions  |
+------------+----------------+----------+---------------+--------------+----------+
| 10         |            18  | Licence  | Licence for.. |France, Bel.. | Europe.. |
| 10         |            13  | Licenz   | Licenz fur..  |France, Bel.. | Europe.. |
| 13         |            10  | Licence  | Licence for.. |Brazil, Arg.. | South .. |
| 15         |            5   | Lice     | Lice f. ir..  |USA, Canada   | North .. |

I have other columns I need from licence not only the ID, but in essence I want to Concat 2 columns from 2 tables while the other data acs as a normal LEFT JOIN

Kaddath · Accepted Answer · 2023-06-22 13:39:00Z

Maybe there is a more optimized solution, but the simplest that comes to mind would be to enclose the grouped query in another query, then join the result with your languages, something like this:

SELECT * FROM (
    SELECT
        l.id,
        GROUP_CONCAT(rc.id_country SEPARATOR ';') AS countries,
        GROUP_CONCAT(rc.id_region SEPARATOR  ';') AS regions
    FROM license l
    LEFT JOIN licence_country lc ON l.id = lc.id_licence
    LEFT JOIN region_country rc ON lc.id_country = rc.id_country
    GROUP BY l.id
) mq
LEFT JOIN license_language ll ON mq.id = ll.id_licence

EDIT: here is a working example in SQL Fiddle, note that I was obliged to correct many thing from the provided query, as the examples of data you provided had different field names than the query (I had already written the tables structure when I noticed, so I stuck to them). Be particularly cautious about mistakes with licence (c) and license (s), if you haven't already, pick one and stick to it for all your tables (I would prefer with a "s" as it is standard english)

There is another solution, but it requires that your language table has a unique identifier: you can GROUP BY using multiple fields, assuring you that the combination of (license,language) is unique:

SELECT
    l.licence_id,
    GROUP_CONCAT(rc.country_id SEPARATOR ';') AS countries,
    GROUP_CONCAT(rc.region_id SEPARATOR  ';') AS regions,
    ll.title, ll.description
FROM licence l
LEFT JOIN licence_countries lc ON l.licence_id = lc.licence_id
LEFT JOIN region_countries rc ON lc.country_id = rc.country_id
LEFT JOIN license_language ll ON l.licence_id = ll.licence_id
GROUP BY l.licence_id,ll.id

example in SQL Fiddle with field id added to the language table

I edited the answer with another solution, that requires you have a unique identifier on the language table

Collectives™ on Stack Overflow

SQL GROUP_CONCAT without GROUP BY?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related