SQL Query to Join Three Tables

Data and Databases
1

I need assistance with constructing an SQL query to fetch data from multiple tables. Below is the query I’ve written so far:

$sqlQuery = "SELECT x.section_id, y.section_id, y.student_id, z.*
              FROM sections AS x
              LEFT JOIN enrollment AS y ON x.section_id = y.section_id
              LEFT JOIN students AS z ON y.student_id = z.student_id
              WHERE y.student_id = z.student_id
              AND x.branch_name = '$branchName'";

Here is the structure of my database tables:

sections:

section_id    section_title    academic_year    teacher_id    grade_level

enrollment:

student_id    section_id

students:

student_id    first_name    last_name

I’m facing challenges in crafting the correct SQL statement and would appreciate any guidance on how to accurately present all students in a specified section.

2

hey there, TalentedSculptor23! have you thought about using INNER JOIN instead of LEFT JOIN here? it could help if you want to ensure only related records are fetched. Also, what results are you seeing right now? Curious if there’s any funny behavior in ther!

3

hey TalentedSculptor23! looks like ur join logic is close, but you might not need the WHERE y.student_id = z.student_id anymore coz u already joined students with enrollment. Try removing it and see if the query works out better!

4

hey, thesqlquery seems fine mostly, maybe try keeping the selefted columns in sync with what you actually need. Also watch out for any typos in table names, those can sneak in pretty easily! good luck fixing it :slightly_smiling_face:

5

Your query seems mostly correct, but I suggest verifying that each section has an associated record in the enrollment table to avoid unexpected null results due to the LEFT JOIN. If you still face issues, try selecting specific columns instead of using z.* for better control over what data you retrieve. Additionally, ensure that the variable $branchName is properly sanitized to prevent SQL injection. Debugging each step by printing out intermediary results can also help you pinpoint where things may not be aligning.

6

Ever think about changing the order of joins? like start with students and work back from there? sometimes a different approach can reveal new pattrens! is your $branchName working properly with other queries? let’s untangle this together! :blush: