Search code examples
mysqlsqlinner-join

How can I put the subject of the students by their Year Level?

student_table

INSERT INTO `student_table` (`student_id`, `name`, `year_level`, `section`) 
VALUES (1001, 'Paul', '4', 'A');    
INSERT INTO `student_table` (`student_id`, `name`, `year_level`, `section`)
 VALUES (1002, 'Jake', '5', 'A');    
INSERT INTO `student_table` (`student_id`, `name`, `year_level`, `section`) 
VALUES (10005, 'John', '4', 'A');

subject_table

INSERT INTO `subject_table` (`sub_id`, `sub_code`, `sub_name`, `year_level`) 
VALUES ('1', '103', 'English', '4');    
INSERT INTO `subject_table` (`sub_id`, `sub_code`, `sub_name`, `year_level`)
 VALUES ('2', '104', 'Math', '5');

year_table

INSERT INTO `year_table` (`year_id`, `year_level`) VALUES ('10', '4');    
INSERT INTO `year_table` (`year_id`, `year_level`) VALUES ('11', '5');

is this possible guys? I just need an brief explanation how can I insert the subjects of the students by their own year level?

Jake must have a subject Math & John & Paul must have a subject English is this possible guys?

Expected Output:

Expected Output

Solution

  • You seem to want a join:

    select st.*, su.subname
from student_table st 
inner join subject_table su on su.year_level = st.year_level

    It doesn't look like you need year_table to produce the results you want.