Query from multiple tables probably simple

I am stumped - I started an ambitious project of creating my own online gradebook and I love the challenge of learning as I go. . but now I am stuck! Here is what I want to do

I need to click on a project title and be presented with a list of all my students and the grades they received for that project. I have two tables, studentGrade that holds project id, student id and grade AND userInfo that holds the student id, names and other info of all my students. This is a join. . . I have done other, simple joins before. . I just can't seem to wrap my brain around what I need to do here. I want all students listed - even if they do not have a grade.

Thank you for your reply - Looking at your code I see userInfo.project_id - I don't have a project_id field in my userInfo tbl? IS this a typo or am I not understanding your join syntax. I tried your code both as you have written it and as my example below

When you do a join, each joined table needs what we call a "join condition". Using the JOIN ... ON ... syntax will make missing join conditions stand out like a sore thumb:

SELECT *
FROM userInfo u
LEFT JOIN studentGrade g ON g.student_id=u.student_id AND g.project_id = 45
LEFT JOIN assignmentInfo a ON a.project_id=g.project_id

First, I gave each table an alias (`a`, `g`, and `u`) to make it easier to write the query.

The condition after "ON" is the join condition, it links the table you are joining into the ones that have come before.

Now, because you want every student to be listed, you should put userInfo first and put LEFT JOINs after it. And since studentGrade is the linking table between students and assignments, it should go second.

Finally, since there is no link for students who haven't been graded, using that "WHERE g.project_id=45" will make ungraded students disappear. So I moved it out of the WHERE and into the LEFT JOIN condition.

Note that the assignment info will be missing on students that aren't graded. It might be best to get the assignment info in a separate query, or you could do it this way:

SELECT *
FROM userInfo u
LEFT JOIN studentGrade g ON g.student_id=u.student_id AND g.project_id = 45
LEFT JOIN assignmentInfo a ON a.project_id=45