Using group by on two tables

I have two tables. One is a subject and the other one is allocated subjects to students. What I need is to use GROUP BY and display all the students in a single column with all subjects.

Table: Subject_Selection

Table: table Student with allocated subjects:

The Final output I want to use GROUP BY:

You can do it using inner join to join data and group by with group_concat to combine data in single column :

select s.Stdid as Student, group_concat(Subject_name) as 'Subject name'
from Student s
inner join Subject_Selection ss on s.subject_id = ss.SubjectID
group by s.Stdid

Ps : this is a working solution for mysql/mariadb

If your are using an other dbms then you have to change group_concat by an equivalent.

Demo here

STEP 1:-JOIN the 2 tables on subject_id so that you get student id and subject name and subject_id on a single tuple.

STEP 2:- just group by student_id and concat the different tuples in a particular group to a single tuple...

select Stdid as Student, group_concat(Subject_name) as 'Subject name' from Student NATURAL JOIN Subject_Selection group by Stdid;

DEMO:-
https://dbfiddle.uk/-YoftCWl

I used natural join since I changed the attribute name to SubjectID in both the tables; otherwise you can use inner join