Question 20105 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL: Group Concat not function

I want to use group_concate in my queries. I have two tables which are wla_user and wla_factory. In table wla_user, the column factory_id contains 1,8, while at wla_factory, id 1= factory 1 while id 8 = factory 8. While during the query, column factory_name, only show factory 1.

What I want is suppose factory 1, factory 8. Below is my current code:

SELECT wla_user.id, wla_user.name, wla_user.email, GROUP_CONCAT(wla_user.factory_id), GROUP_CONCAT(wla_factory.factory_name)
FROM wla_user 
INNER JOIN wla_factory ON wla_user.factory_id = wla_factory.id 
WHERE wla_user.email = '[email protected]' AND wla_user.status = 1 GROUP BY wla_user.id

Can anyone know which part I missed?

Tags:

2

Answers


  1. 0

    You are almost there, your refined query will be:

    SELECT wla_user.id, wla_user.name, wla_user.email, GROUP_CONCAT(DISTINCT wla_user.factory_id), GROUP_CONCAT(DISTINCT wla_factory.factory_name)
FROM wla_user 
INNER JOIN wla_factory ON wla_user.factory_id = wla_factory.id 
WHERE wla_user.email = '[email protected]' AND wla_user.status = 1 GROUP BY wla_user.id

    The resource to refer to:
    https://www.geeksforgeeks.org/mysql-group_concat-function/

    Hope this helps.

    Login or Signup to reply.
  2. 0

    I am not 100% sure that my understanding of your question is correct. I am assuming the below sample data with CSV value for wla_user.factory_id:

    wla_user

    id name email status factory_id
    1 user 1 [email protected] 1 1,8
    2 user 2 [email protected] 1 2,7

    wla_factory

    id factory_name
    1 factory 1
    2 factory 2
    … …
    7 factory 7
    8 factory 8

    Your current join wla_user.factory_id = wla_factory.id leads to wla_user.factory_id being implicitly cast to an integer, which is why you are only seeing one value from the wla_factory table.

    As pointed out by Akina, you can use FIND_IN_SET() to join based on your CSV column:

    SELECT wla_user.id, wla_user.name, wla_user.email, wla_user.factory_id, GROUP_CONCAT(wla_factory.factory_name)
FROM wla_user
INNER JOIN wla_factory ON FIND_IN_SET(wla_factory.id, wla_user.factory_id)
WHERE wla_user.email = '[email protected]'
AND wla_user.status = 1
GROUP BY wla_user.id

    The above query will work but, again as pointed out by Akina, you should normalise your data. Working with serialised data in columns is problematic (hard to query, cannot use indices, etc) when you need to query based on that data. Instead you should move the relation to a many-to-many table:

    CREATE TABLE wla_user_factory (
    user_id INT UNSIGNED NOT NULL,
    factory_id SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, factory_id),
    INDEX (factory_id, user_id),
    FOREIGN KEY (user_id) REFERENCES wla_user (id),
    FOREIGN KEY (factory_id) REFERENCES wla_factory (id)
);
    user_id factory_id
    1 1
    1 8
    2 2
    2 7

    Then your query becomes:

    SELECT
    u.id, u.name, u.email,
    GROUP_CONCAT(f.id ORDER BY f.id) AS factory_ids,
    GROUP_CONCAT(f.factory_name ORDER BY f.id) AS factory_names
FROM wla_user u
INNER JOIN wla_user_factory uf ON u.id = uf.user_id
INNER JOIN wla_factory f ON uf.factory_id = f.id
WHERE u.email = '[email protected]'
AND u.status = 1
GROUP BY u.id;

    And the output is:

    id name email factory_ids factory_names
    1 user 1 [email protected] 1,8 factory 1,factory 8

    Here’s a db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search