topimiring topimiring - 7 months ago 7
SQL Question

combine information from two tables into one table

I need to combine information from two tables into one table, the following table is

table 1
+----+---------------+---------+
|id_k| name | value |
+----+---------------+---------+
| 1 | enak | 4 |
| 2 | nor | 3 |
+----+---------------+---------+

table 2
+------+------+---------+
| id_d | id_k | feel |
+------+------+---------+
| 1 | 1 | good |
| 2 | 1 | better |
| 3 | 1 | verygood|
+------+------+---------+


result should be

+------+------+-------+------------------------+
| id_d | name | value | feel |
+------+------+-------+------------------------+
| 1 | enak | 4 | good, better, verygood |
| 2 | nor | 3 | |
+------+------+-------+------------------------+


this is my code [not worked]

select k.name, k.value, s.feel
from table1 as k
left join table2 as s on s.id_k=k.id_k

Answer

http://sqlfiddle.com/#!9/3a7564/1

SELECT   t1.id_k, 
  t1.`name`,
  t1.`value`, 
  GROUP_CONCAT(t2.feel) AS feel
FROM     table1 t1
LEFT JOIN table2 t2
ON t1.id_k = t2.id_k
GROUP BY t1.id_k
Comments