CODEWITHSUNDEEP
mysql
Zeddicus
Zeddicus

Reputation: 1

Combine two table mysql

Is it Possible to combine these two table like this? All Subjects from table2 should be combined from table1 and the subjects should be aligned in 1 row with the same ID from table1.

Table1 
----------------------
ID   Student_ID   Name

 1   200000      Paul

 2   202907      Ger


Table2
----------------------
Student_ID   Subject

200000       ACT111

200000       ACT112

200000       ACT113

202907       ACT111

-------------------


Expected Combination
------------------------------------------------------
ID       Student_ID     Name            Subject
1        200000         Paul      ACT111 ACT112 ACT113

Upvotes: 0

Views: 43

Answers (2)

Kleskowy
Kleskowy

Reputation: 2668

Just use a simple JOIN (or LEFT JOIN, if you expect to be no entries in Table2 for entries in Table1), along with a GROUP_CONCAT function:

SELECT t1.ID, t1.Student_ID, t1.Name, GROUP_CONCAT(DISTINCT t2.Subject SEPARATOR ' ') as Subject
FROM Table1 t1 
JOIN Table2 t2 ON t1.Student_ID = t2.Student_ID
GROUP BY t1.Student_ID

This takes rows from Table1 and adds data from rows from Table2, that have same Student_ID.

If there is no entry in Table2 for a specific student, but you still want it to be visible in the result, just replace JOIN with LEFT JOIN.

Read more about JOINs in mysql docs and function GROUP_CONCAT.

Upvotes: 0

Barranka
Barranka

Reputation: 21047

You must relate your tables to get the data (see JOIN syntax), and then you can use group_concat() to get the value list separated by commas:

select t1.id, t1.student_id, t1.name
     , group_concat(t2.subject separator ',') as subjects
from table1 as t1
     inner join table2 as t2 on t1.student_id = t2.student_id
--   ^^^^^^^^^^ Replace 'INNER JOIN' with 'LEFT JOIN' if you want to
--              also show the students with no subjects
group by t1.id;

Upvotes: 1

Related Questions