0

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

zmeutz
  • 37
  • 2
  • 10
  • You don't have an `a` alias anyways, so the query would be a flat-out syntax error `a.wpeople` won't exist. And why didn't you normalize your tables from the get-go? Storing multiple data values in a second field is almost a sign of a bad design. find_in_set() is highly inefficient, since indexes can't be used. – Marc B Jun 02 '16 at 18:19
  • As said above, you need to denormalize that data. That said, formatting the output like this is best left to the presentation layer rather than the data layer. What are you using to show the data? A web page? An app? – Matt Runion Jun 02 '16 at 18:28
  • @ Marc My bad, I forget ... to change it, in my code it's a and b, here I made the change e from employees and b form workorder @mrunion I'm using this SQL interrogation for an application, used to administrate jobs for employeers and at the end to send an email to let them know... – zmeutz Jun 02 '16 at 18:40
  • irrelevant. that doesn't prevent you from normalizing the design. – Marc B Jun 02 '16 at 18:42
  • So if you're sending an email, some code is taking the data results and putting them into the email body and sending the email, right? Make that code take the results in row form (how they will be once you properly denormalize the data as Marc B says) and loop through them to get your output in the form you want. – Matt Runion Jun 02 '16 at 18:46
  • @MarcB You are saying that, it's more better to user directly the name not the id of the user? The email will not be received by employees ... – zmeutz Jun 02 '16 at 18:52
  • no. I'm saying the structure of your db is wrong. you should never be storing in `1,2,3` in a single field. you should have a child table, where you have a `employee_id` field, and have three records, with `1`, `2`, and `3` in them. – Marc B Jun 02 '16 at 18:53
  • @MarcB Thanks! I will make another table where I will put each record – zmeutz Jun 02 '16 at 19:19

1 Answers1

0

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

zmeutz
  • 37
  • 2
  • 10