troubleshooting Question

MySQL Query Syntax Especially When Using JOIN

Avatar of Opeyemi AbdulRasheed
Opeyemi AbdulRasheed asked on
PHPSQL
8 Comments2 Solutions78 ViewsLast Modified:
Hello Experts!

I need help on the following:

I have two tables - tbl_students and tbl_subjects_enrollment

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.CLASS_NAME.

tbl_subjects_enrollment equally has Student_ID column.

Something like:
tbl_students
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

tbl_subjects_enrollment (not empty)
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

I want the result of SELECT for Assessment Table (DataTable) to look like the following when Class_Name=1A, Subject=ENG, Current_Session=2018, Current_Term=1st.
Student_ID  CA1
0001        7 
0002         
0003
0004
0005 

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.
"SELECT 
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
, tbl_subjects_enrollment.CA1 
FROM tbl_students 
LEFT JOIN tbl_subjects_enrollment 
ON tbl_students.Student_ID=tbl_subjects_enrollment.Student_ID 
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros