I need help on the following:
I have two tables - tbl_students
I want to SELECT all students using these columns (Student_ID, Student_Name, Roll_No) from tbl_students WHERE the CLASS_NAME=?
And JOIN with (CA1) column from tbl_subjects_enrollment WHERE Session=? AND Term=? AND Class_Name=tbl_students.CL
tbl_subjects_enrollment equally has Student_ID column.
tbl_subjects_enrollment (not empty)
Student_ID Student_Name Roll_No Class_Name Current_Session Current_Term
0001 AAAA 1 1A 2018 1st
0002 BBBB 2 1A 2018 1st
0003 CCCC 3 1A 2018 1st
0004 DDDD 4 1A 2018 1st
0005 EEEE 5 1A 2018 1st
0006 FFFF 1 1B 2018 1st
0007 GGGG 2 1B 2018 1st
I want the result of SELECT for Assessment Table (DataTable)
Enroll_ID Student_ID Class_Name Subject Session Term CA1
1 0001 1A ENG 2018 1st 7
2 0002 1A MTH 2018 1st 6
3 0001 1A ENG 2018 2nd 9
to look like the following when Class_Name=1A, Subject=ENG, Current_Session=2018, Current_Term=1st.
As we can see in the Assessment Table above, ALL students from 1A are listed but only Student 0001 has score (7) in ENG yet, others are with no scores. The CA1 field in the DataTable is a Text Field where user can either update or input new scores.
I have tried the folowing but only returns students with scores (those already in the tbl_subjects_enrollment) without regard for Session and Term.
tbl_students.Student_ID AS Student_ID
, tbl_students.Student_Name AS Student_Name
, tbl_students.Class_Name AS Class_Name
, tbl_students.Roll_No AS Roll_No
, tbl_subjects_enrollment.Enroll_ID AS Enroll_ID
, tbl_subjects_enrollment.Subject_Code AS Subject_Code
LEFT JOIN tbl_subjects_enrollment
WHERE tbl_students.Default_Session = ?
AND tbl_students.Default_Term = ?
AND tbl_students.Class_Name = ?
AND tbl_subjects_enrollment.Subject_Code = ?
AND tbl_students.Status = 1
ORDER BY tbl_students.Roll_No ASC
LIMIT ? "
I hope I'm clear enough.
Thank you for your help.