MySQL Query Syntax Especially When Using JOIN

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

Open in new window


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

Open in new window


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 

Open in new window


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 ? "

Open in new window

I hope I'm clear enough.
Thank you for your help.
LVL 1
Opeyemi AbdulRasheedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
try this instead:

"SELECT 
a.Student_ID AS Student_ID
, a.Student_Name AS Student_Name
, a.Class_Name AS Class_Name
, a.Roll_No AS Roll_No
, b.Enroll_ID AS Enroll_ID
, b.Subject_Code AS Subject_Code
, b.CA1 
FROM tbl_students a
LEFT JOIN 
(
	Select * from tbl_subjects_enrollment
	Where Subject_Code = ? 
) b	
ON a.Student_ID=b.Student_ID 
WHERE a.Default_Session = ? 
AND a.Default_Term = ? 
AND a.Class_Name = ? 
AND a.Status = 1 
ORDER BY a.Roll_No ASC 
LIMIT ? "

Open in new window

Opeyemi AbdulRasheedAuthor Commented:
Thank you @Ryan Chong,

No record returned. When I checked Developer Tools in Chrome, I got
[]

Open in new window

(No Properties)
Ryan ChongSoftware Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
No record returned. When I checked Developer Tools in Chrome, I got
also check and make sure correct parameters have been passed in your php code.

you need to debug from there.

the SQL should just working fine, you can test it outside of php codes with fixed values and see if got records returned or not.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Opeyemi AbdulRasheedAuthor Commented:
You were so correct, sir. The parameter binding was now properly done. So I re-arranged.

However, it returned all records without minding the Session and Term, so I adjusted as follows which returned desired result.
"SELECT 
a.Student_ID AS Student_ID
, a.Student_Name AS Student_Name
, a.Class_Name AS Class_Name
, a.Roll_No AS Roll_No
, b.Enroll_ID AS Enroll_ID
, b.Subject_Code AS Subject_Code
, b.Session AS Session
, b.Term AS Term
, b.CA1 
FROM tbl_students a
LEFT JOIN 
(
	Select * from tbl_subjects_enrollment
	Where Subject_Code = ? 
) b	
ON a.Student_ID=b.Student_ID AND a.Default_Session=b.Session AND a.Default_Term=b.Term
WHERE a.Default_Session = ? 
AND a.Default_Term = ? 
AND a.Class_Name = ? 
AND a.Status = 1 
ORDER BY a.Roll_No ASC 
LIMIT ? "

Open in new window

Please help me check if I did it well.
Ryan ChongSoftware Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
so I adjusted as follows which returned desired result.

ok great, just make sure the variable binding in the php code is adjusted accordingly
Opeyemi AbdulRasheedAuthor Commented:
Thank you so much sir. How can I avoid null in the input box of those without scores?
Ryan ChongSoftware Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
How can I avoid null in the input box of those without scores?

probably you can create a new question and discuss from there? it seems that your latest question not really related to your original question.
Opeyemi AbdulRasheedAuthor Commented:
Ok. I'll do just that
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.