CODEWITHSUNDEEP
mysqlsqljoin
George B.
George B.

Reputation: 312

SQL (JOIN) - two result from one column to two column

SELECT a.cp_node_id,
       a.cmi_node_id,
       a.user_id,
       a.learner_name,
       a.completion_status,
       a.success_status,
       b.id title,
       c.dep,
       d.field_id,
       d.value
       FROM cmi_node a
JOIN cmi_objective b
  ON a.cmi_node_id=b.cmi_node_id
 AND a.completion_status = 'completed'
 AND a.success_status = 'passed'
 AND a.cp_node_id = '1'
JOIN usr_data c
  ON a.user_id = c.usr_id
JOIN udf_text d
  ON a.user_id = d.usr_id
GROUP BY a.cmi_node_id

In my "D" table I have one column, there is two result, example:

ID: field_id:  value:
1   1          test
1   2          text2
2   1          test
2   2          text3
3   1          test2
3   2          text6
etc...

In field_in always are 1 or 2.

I need print ALL my users from "cmi_node", example:

user_id  learner_name
1        George
2        Michel
3        James

And I want to add two column from "D" table: example:

user_id  learner_name  field_in_first field_in_second value_first value_second
1        George        1              2               test        text2
2        Michel        1              2               test        text3
3        James         1              2               test2       text6

Thank you

Upvotes: 1

Views: 69

Answers (1)

fafl
fafl

Reputation: 7385

Just join the same table two times.

JOIN udf_text d1 ON a.user_id = d.usr_id AND field_id = 1
JOIN udf_text d2 ON a.user_id = d.usr_id AND field_id = 2

then you can access d1.field_id, d2.field_id, d1.value and d2.value

Upvotes: 1

Related Questions