Search code examples
left-joingroup-concatfind-in-set

Select multiple data from other table for each id separated by comma

Table one - workorder

╔══════════╦══════════════╦══════════════╗
║ id       ║    wpeople   ║  start_date  ║
╠══════════╬══════════════╬══════════════╣
║ 1        ║ 1,2,4        ║  02.08.2016  ║
║ 2        ║ 4,5          ║  28.09.2016  ║
╚══════════╩══════════════╩══════════════╝

Table two - employees

╔══════════╦═════════════════╗
║ id       ║    name         ║
╠══════════╬═════════════════╣
║ 1        ║ John            ║
║ 2        ║ Ben             ║
║ 3        ║ Ian             ║
║ 4        ║ Hank            ║
║ 5        ║ George          ║
╚══════════╩═════════════════╝

Output selection for who need to work at the project

╔══════════╦════════════════╦════════════╗
║ 1        ║ John,Ben,Hank  ║ 02.08.2016 ║
║ 2        ║ Hank,George    ║ 28.09.2016 ║
╚══════════╩════════════════╩════════════╝

I have tried with GROUP_CONCAT and FIND_IN_SET

SELECT  w.id,
        GROUP_CONCAT(e.name ORDER BY e.id) workorder
FROM    workorder w
        INNER JOIN employees e
            ON FIND_IN_SET(e.id, a.wpeople) > 0
GROUP   BY w.id

But the output it's

╔══════════╦════════════════╦════════════╗
║ 1        ║ John           ║ 02.08.2016 ║
║ 1        ║ Ben            ║ 02.08.2016 ║
║ 1        ║ Hank           ║ 02.08.2016 ║
║ 2        ║ Hank           ║ 28.09.2016 ║
║ 2        ║ George         ║ 28.09.2016 ║
╚══════════╩════════════════╩════════════╝

I search on google for this and the solution it's GROUP_CONCAT - FIND_IN_SET. Can be that I didn't understand very well this function.

Thanks for you time! Stefan

Solution

  • For anyone who will need this: I added a new table werkbon_employee

    ╔══════════╦═══════════════════╦═══════════════╗
║ id       ║    workorder_id   ║  employee_id  ║
╠══════════╬═══════════════════╬═══════════════╣
║ 1        ║ 1                 ║  1            ║
║ 2        ║ 1                 ║  2            ║
║ 3        ║ 1                 ║  4            ║
║ 4        ║ 2                 ║  4            ║
║ 5        ║ 2                 ║  5            ║
╚══════════╩═══════════════════╩═══════════════╝

    I used to select

    SELECT  *,
        GROUP_CONCAT(e.name ORDER BY e.id) ename
FROM werkbon
        LEFT JOIN werkbon_employee we ON werkbon.id = we.werkbon_id
        INNER JOIN employees e ON FIND_IN_SET(e.id, we.employee_id) > 0
GROUP BY werkbon.id DESC LIMIT 1

    Now the result it's

    Werk mensen
John,Ben,Hank

Datum
02.08.2016

    Thanks to @MarcB for help