Query from multiple tables probably simple

[tgelston]

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.

example output would look something like

john smith 89

mary jane

Tom Kat 54

I have this

[code]$query='SELECT * FROM userInfo, studentGrade, assignmentInfo WHERE studentGrade.project_id = 45 AND assignmentInfo.project_id = 45';[/code]

But it outputs all students 3x with the grade filled in for every student

Hints, suggestion and tips would all be very much appreciated.





Thank You,
Tobias

[jvrothjr]

[code]
$query = ("select * from userInfo
    left join studentGrade on userInfo.project_id=studentGrade.project_id
    where studentGrade.project_id = 45");
[/code]

[tgelston]

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


[!--quoteo(post=360404:date=Mar 31 2006, 11:34 AM:name=jvrothjr)--][div class=\'quotetop\']QUOTE(jvrothjr @ Mar 31 2006, 11:34 AM) [snapback]360404[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[code]
$query = ("select * from userInfo
    left join studentGrade on userInfo.project_id=studentGrade.project_id
    where studentGrade.project_id = 45");
[/code]
[/quote]


[code]$query = 'SELECT * from assignmentInfo, userInfo LEFT JOIN studentGrade on assignmentInfo.project_id=studentGrade.project_id WHERE studentGrade.project_id = 45';
[/code]

But I still get the output three times. . all students are listed.. . I need to go read up on joins?

I appreciate the help!

[wickning1]

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:

[code]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[/code]
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:

[code]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[/code]

[tgelston]

Perfect - Thank you very much! I appreciate you taking the time to explain your answer.