-2

I am trying to get the role_name from role table using the roles column in my individual table.

This is what's inside my role table:

role_id | role_name
____________________
1       |Participant
2       |Respondent
3       |Survey Previewer
4       |Coach
5       |Faculty Viewer
6       |Hr
7       |Client Admin

I am trying to left join the role_name in my individual table using values from roles column, this is my roles column inside individual table, and these numbers are from inputted using role_id:

roles
______
1, 3        |
1, 4, 5, 7  |
2, 4, 5     |

I tried using this query:

export const getAllIndividuals = (result) => {
  db.query(
    "SELECT (GROUP_CONCAT(role.role_name SEPARATOR',')) AS _roles_name, individual.* FROM 
individual LEFT JOIN role ON role.role_id = individual.roles GROUP BY individual.ind_id",
    (err, results) => {
      if (err) {
        console.log(err);
        result(err, null);
      } else {
        result(null, results);
      }
    }
  );
};

but this query only gives me the result of the first number in my roles column, you can see it in this example :

first example

second example

How can I return all the role_name I need based on my roles column?

Three_L_Some
  • 3
  • 3
  • 1
    please read https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question and https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query and edit your qiestion – nbk May 12 '22 at 21:29
  • The accepted answer to the duplicate question is the correct one: redesign your table structures. – Shadow May 12 '22 at 21:33
  • Please use text, not images/links, for text--including tables & ERDs. [SO](https://meta.stackoverflow.com/q/285551/3404097) [SE](https://meta.stackexchange.com/q/320052/266284) Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. Please in code questions give a [mre]. Research before considering asking & reflect research in a question. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. – philipxy May 12 '22 at 22:51

0 Answers0