Jump to content

Archived

This topic is now archived and is closed to further replies.

tgelston

Query from multiple tables probably simple

Recommended Posts

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

Share this post


Link to post
Share on other sites
[code]
$query = ("select * from userInfo
    left join studentGrade on userInfo.project_id=studentGrade.project_id
    where studentGrade.project_id = 45");
[/code]

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
Perfect - Thank you very much! I appreciate you taking the time to explain your answer.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.