Foreign Keys separated in one field

Question

I have two tables :

tbllocations

ID      |      Name      |  Type
1       | Location1      |  {"0":"23","1":"27","2":"24","3":"22"}
2       | Location2      |  {"0":"22","1":"25"}

tbllocationtypes

ID      |      title      
22      |    Wines
23      |    Milk
24      |    Cereals
25      |    Drinks
26      |    Beef
27      |    Chicken

In the Type field (with this exact format) the foreign keys of types are specially separated

"0":"Default Foreign Key of tbllocationtype", "1","First additional Foreign Key of tbllocationtype", "2","Second Additional Foreign Key of tbllocaitontype"

I need to have a list with all location with a field which contains all types like this :

Result of query :

IDLocation     |   Name       |     Types
1                Location1       Milk,Chicken,Cereals,Wine
2                Location2       Wines,Drinks

Could you please help me? I'm too bad in MySQL to find that out.

Thanks a lot

['Using MySQL JSON field to join on a table'](https://stackoverflow.com/q/39818296/4137828) is a similar question. See also the link to the JSON search function in the comment. — Henrik, Aug 13 '18 at 15:15

wchiquito · Answer 1 · 2018-08-15T13:23:37.460

In modern versions of MySQL (>= 8.0.4) the query would be relatively simple:

SELECT
  `l`.`id` `IDLocation`,
  `l`.`name` `Name`,
  GROUP_CONCAT(`lt`.`title`) `Types`
FROM
  `tbllocations` `l`,
  JSON_TABLE(`l`.`type`,
    '$.*' COLUMNS(
      `id` BIGINT UNSIGNED PATH '$'
    )
  ) `der`
  INNER JOIN `tbllocationtypes` `lt` ON
    `lt`.`id` = `der`.`id`
GROUP BY
  `l`.`id`,
  `l`.`name`;

See db-fiddle.

However, in older versions, it's not that simple, one option:

SELECT
  `l`.`id` `IDLocation`,
  `l`.`name` `Name`,
  GROUP_CONCAT(`lt`.`title`) `Types`
FROM
  `tbllocationtypes` `lt`
  LEFT JOIN
    `tbllocations` `l` ON
      JSON_CONTAINS(
        JSON_EXTRACT(`l`.`type`, '$.*'),
        JSON_QUOTE(CAST(`lt`.`id` AS CHAR))
      )
WHERE
  `l`.`id` IS NOT NULL
GROUP BY
  `l`.`id`,
  `l`.`name`;

See db-fiddle.

In any case, remember 5.1.7 Server System Variables::group_concat_max_len.

Foreign Keys separated in one field

1 Answers1