how to do joins correctly

[jgalzic]

Hi,
I'm having trouble doing joins correctly on two tables. I've read up a
lot about the different types of joins and tried lots of variations on
inner, outer, and left joins with no avail. Something isn't correct
with my logic so could anyone give me some pointers on it?

I have 2 tables:

CourseRoster:
ID CourseID StudentID StudentType
01 1000 1000 FT
02 1000 1001 PT
03 1000 1002 FT
04 1000 1003 FT
05 1000 1004 FT
06 1000 1005 FT
07 1000 1006 PT
08 1001 1007 PT
09 1001 1008 FT

CourseExams:
ID ExamID CourseID StudentID Grade
80 2000 1000 1000 95
81 2000 1000 1001 87
82 2000 1000 1002 98
83 2001 1001 1004 74
84 2001 1001 1000 88

How can I set up a query so that I get all students in Course = 1000
and their Grades for ExamID = 2000 are shown even for the students who
do not have one? What I want to get is:

CourseID ExamID StudentID Grade
1000 2000 1000 95
1000 2000 1001 87
1000 2000 1002 98
1000 2000 1003 NULL
1000 2000 1004 NULL
1000 2000 1005 NULL
1000 2000 1006 NULL
Any thoughts and suggestions would be much appreciated.

Justin

[Thomas Bartkus]

<jg*****@hotmai l.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...

Hi,
I'm having trouble doing joins correctly on two tables. I've read up a
lot about the different types of joins and tried lots of variations on
inner, outer, and left joins with no avail. Something isn't correct
with my logic so could anyone give me some pointers on it?

Things do get complicated fast - don't they?

The way I approach this is with 2 or more simpler queries. Temporary tables
make it simple to do cascading queries.
First I want to isolate the grades for Course=1000, ExamId = 2000

CREATE TEMPORARY TABLE Grades
SELECT StudentID, Grade
FROM CourseExams
WHERE (CourseID=1000) AND (ExamID=2000);

This creates a temporary table of just the grades we are interested in. In
this case, we get (3) grades that qualify from your example data.
Next -
I gather you want to list *all* students taking course 1000 and, *if* they
have a grade for ExamId 2000, you will show that grade. That would mean
taking the CourseRoster of all students enrolled in course 1000 and then
doing a LEFT JOIN with the temporary table of grades you just created.

SELECT 1000 AS CourseID, 2000 AS ExamID, CourseRoster.St udentID,
Grades.Grade
FROM CourseRoster
LEFT JOIN Grades ON CourseRoster.St udentID=Grades. StudentID
WHERE (CourseRoster.C ourseID=1000);

The LEFT JOIN gaurantees that you will see *all* the students, whether they
have a grade or not. LEFT JOIN means you will get a NULL if a grade doesn't
exist for that student. Notice the WHERE clause restricts to only those
students taking CourseID=1000.

I used constant values for CourseID and ExamID in the second query. Our
queries gaurantee that this will be so. Note that, had we drawn the ExamID
from our table join, it would show the ExamID as NULL for those entries
where no entry for that student exists in table CourseExams. Using constants
here is simpler, avoids that problem, and yields exactly the results you
specified.

Now be tidy and do a
DROP TABLE Grades;
Even though the system will drop temporary tables for you as soon as you
disconnect, it pays to be neat and complete.

Cheers!
Thomas Bartkus

[Bill Karwin]

jg*****@hotmail .com wrote:

Hi,
I'm having trouble doing joins correctly on two tables. I've read up a
lot about the different types of joins and tried lots of variations on
inner, outer, and left joins with no avail. Something isn't correct
with my logic so could anyone give me some pointers on it?

I have 2 tables:

CourseRoster:
ID CourseID StudentID StudentType
01 1000 1000 FT
02 1000 1001 PT
03 1000 1002 FT
04 1000 1003 FT
05 1000 1004 FT
06 1000 1005 FT
07 1000 1006 PT
08 1001 1007 PT
09 1001 1008 FT

CourseExams:
ID ExamID CourseID StudentID Grade
80 2000 1000 1000 95
81 2000 1000 1001 87
82 2000 1000 1002 98
83 2001 1001 1004 74
84 2001 1001 1000 88

How can I set up a query so that I get all students in Course = 1000
and their Grades for ExamID = 2000 are shown even for the students who
do not have one? What I want to get is:

CourseID ExamID StudentID Grade
1000 2000 1000 95
1000 2000 1001 87
1000 2000 1002 98
1000 2000 1003 NULL
1000 2000 1004 NULL
1000 2000 1005 NULL
1000 2000 1006 NULL
Any thoughts and suggestions would be much appreciated.

You don't have an adequate data model for that query. If there is no
matching row in CourseExams, then the query will return exactly one row
per student. You can't get extra rows for the exams the student hasn't
taken.

You need to add another table:

CourseExamRoste r:
CourseID ExamID
1000 2000
1000 2001
1001 2001

That is, one row per course/exam combination. Then try this query:

SELECT R.CourseID, E.ExamID, S.StudentID, X.Grade
FROM CourseRoster AS R INNER JOIN CourseExamRoste r AS E
ON (R.CourseID = E.CourseID)
LEFT OUTER JOIN CourseExams AS X
ON (R.CourseID = X.CourseID
AND E.ExamID = X.ExamID
AND R.StudentID = X.StudentID)
WHERE R.CourseID = 1000

If you are using MySQL 4.1, you could use a subquery to do a little
trick to form the CourseExamRoste r table on the fly instead of storing
the course/exam combinations:

SELECT R.CourseID, E.ExamID, S.StudentID, X.Grade
FROM CourseRoster AS R INNER JOIN
(SELECT DISTINCT X2.ExamID, X2.CourseID FROM CourseExams X2) AS E
ON (R.CourseID = E.CourseID)
LEFT OUTER JOIN CourseExams AS X
ON (R.CourseID = X.CourseID
AND E.ExamID = X.ExamID
AND R.StudentID = X.StudentID)
WHERE R.CourseID = 1000

But note this will leave out an exam if _none_ of the students took it.
And you might find other cases where it's handy to have a table that
stores the list of course/exams.

Regards,
Bill K.

[Rich R]

<jg*****@hotmai l.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...

Hi,
I'm having trouble doing joins correctly on two tables. I've read up a
lot about the different types of joins and tried lots of variations on
inner, outer, and left joins with no avail. Something isn't correct
with my logic so could anyone give me some pointers on it?

I have 2 tables:

CourseRoster:
ID CourseID StudentID StudentType
01 1000 1000 FT
02 1000 1001 PT
03 1000 1002 FT
04 1000 1003 FT
05 1000 1004 FT
06 1000 1005 FT
07 1000 1006 PT
08 1001 1007 PT
09 1001 1008 FT

CourseExams:
ID ExamID CourseID StudentID Grade
80 2000 1000 1000 95
81 2000 1000 1001 87
82 2000 1000 1002 98
83 2001 1001 1004 74
84 2001 1001 1000 88

How can I set up a query so that I get all students in Course = 1000
and their Grades for ExamID = 2000 are shown even for the students who
do not have one? What I want to get is:

CourseID ExamID StudentID Grade
1000 2000 1000 95
1000 2000 1001 87
1000 2000 1002 98
1000 2000 1003 NULL
1000 2000 1004 NULL
1000 2000 1005 NULL
1000 2000 1006 NULL
Any thoughts and suggestions would be much appreciated.

Justin

Hi Justin,
I agree with Bill Karwin. Your data model needs some work. For example,
StudentType is best stored in a table called Student rather than with
CourseRoster. The way you have it there is a non-key dependency between
StudentNumber and StudentType which will cause headaches down the road. Get
a good book on data modeling and also learn something about normalization.
But anyway, if you're stuck with the design you have, then this will work:

SELECT DISTINCT t1.CourseID,t1. ExamID,t2.stude ntID,t3.grade FROM CourseExams
AS t1
INNER JOIN CourseRoster as t2 ON t2.CourseID = t1.CourseID
LEFT OUTER JOIN CourseExams as t3 on t3.studentID = t2.StudentID AND
t3.ExamID = t1.ExamID
WHERE T1.CourseID = 1000 AND T1.ExamID = 2000

It's basically creating lookup tables on the fly and then joining them.

Regards
Rich

[Thomas Bartkus]

"Bill Karwin" <bi**@karwin.co m> wrote in message
news:cq******** @enews2.newsguy .com...

jg*****@hotmail .com wrote: <snip> You can't get extra rows for the exams the student hasn't
taken.

Then turn the problem around!

You retrieve *all* the students in the course, regardless of whether they
have a grade for that the exam or not. You can then LEFT JOIN along
StudentID to recover the smaller set of exam grades. No grade means that
student gets Null. But records for all the students get returned.

Thomas Bartkus

[Bill Karwin]

Thomas Bartkus wrote:

You retrieve *all* the students in the course, regardless of whether they
have a grade for that the exam or not. You can then LEFT JOIN along
StudentID to recover the smaller set of exam grades. No grade means that
student gets Null. But records for all the students get returned.

Very good suggestion, Thomas! The only small problem is that you can't
take the ExamID from the table; you have to list it as a literal.
Otherwise it'll result in NULLs because of the outer join.

SELECT R.CourseID, '2000' AS ExamID, R.StudentID, X.Grade
FROM CourseRoster AS R LEFT OUTER JOIN CourseExams AS X
ON (R.StudentID = X.StudentID AND R.CourseID = X.CourseID
AND X.ExamID = 2000)
WHERE R.CourseID = 1000

Regards,
Bill K.

[Thomas Bartkus]

On Fri, 31 Dec 2004 02:31:24 -0800, Bill Karwin wrote:

Thomas Bartkus wrote:

You retrieve *all* the students in the course, regardless of whether they
have a grade for that the exam or not. You can then LEFT JOIN along
StudentID to recover the smaller set of exam grades. No grade means that
student gets Null. But records for all the students get returned.

Very good suggestion, Thomas! The only small problem is that you can't
take the ExamID from the table; you have to list it as a literal.
Otherwise it'll result in NULLs because of the outer join.

This is true. You do have to list the ExamID as a literal for the reason
you stated. But this is hardly problematical. The stated conditions are
such that both CourseID and ExamID are constants by design. Under these
circumstances "1000 As CourseID" and "2000 As ExamID" would be perfectly
acceptable.

Apologies for being so nit picky!
Thomas Bartkus