Josh Trutwin <[EMAIL PROTECTED]> wrote on 09/20/2004 10:41:46 PM: > On Mon, 20 Sep 2004 10:25:16 -0400 > [EMAIL PROTECTED] wrote: > > > I think you missed my point. I think the 5.0.1 behavior was correct > > and the others are wrong. There is a known bug (or two) about mixing > > outer joins and inner joins and it looks like it may be fixed. IF > > you want to see all of the students THAT TABLE (students) needs to > > be on the LEFT side of a LEFT JOIN or the RIGHT side of a RIGHT > > JOIN. That's what the directions mean.... > > Interesting - do you have a link to more information on this bug? >
http://bugs.mysql.com/1677 and http://bugs.mysql.com/1591 and http://bugs.mysql.com/3765 > > SELECT s.tech_id, s.full_name, sa.points_awarded, sa.date_submitted > > FROM student s > > INNER JOIN enrollment e > > ON e.tech_id = s.tech_id > > AND e.term_id = '3' > > LEFT JOIN submitted_assignment sa > > ON sa.tech_id = s.tech_id > > LEFT JOIN assignment a > > ON a.a_id = sa.a_id > > AND a.a_id = '100' > > ORDER BY s.full_name; > > > > I also moved the clause "AND a.a_id = '100'" into the ON portion of > > the LEFT JOIN. That way you can see who did and didn't get that > > assignment. > > > > If you describe what you WANT to see, I can help you write the query > > to get it. What I think I wrote for you will be "all students where > > term_ID=3 and what grades they got on assignment 100." But i think > > you may get some duplicate rows of blank scores. Does "assignment" > > relate to "student", perhaps with a tech_id or student_id field? > > That fixes one dilemma by setting up the following query > > > > SELECT s.tech_id, s.full_name, if(isnull(a.a_ID),'no','yes') as > > assigned_100, sa.points_awarded, sa.date_submitted > > FROM student s > > INNER JOIN enrollment e > > ON e.tech_id = s.tech_id > > AND e.term_id = '3' > > LEFT JOIN assignment a > > ON a.student_ID = s.student_ID > > AND a.a_id = '100' > > LEFT JOIN submitted_assignment sa > > ON sa.tech_id = s.tech_id > > AND a.a_id = sa.a_id > > ORDER BY s.full_name; > > This is what I eventually used: > > SELECT s.tech_id, s.full_name, > sa.points_awarded, sa.date_submitted > FROM student s > INNER JOIN enrollment e > ON e.tech_id = s.tech_id > LEFT JOIN submitted_assignment sa > ON sa.tech_id = s.tech_id AND sa.a_id = '$a_id' > LEFT JOIN assignment a > ON a.a_id = sa.a_id > WHERE e.term_id = '3' > ORDER BY s.full_name; > > It didn't seem to work with the "AND sa.a_id = '$a_id'" in the > assignment join condition - but this works. I don't understand why > that doesn't filter the right rows if it's in the WHERE clause, I > usually define my JOIN condition solely on the column(s) being > joined together and any additional filtering gets done in the WHERE clause. > There are up to three layers of record filtering that happen during any query. First is the JOIN filtering. That is where the ON conditions are used with the table declarations to build a virtual table that consists of all columns from each of the participating tables and each combination of rows that meets the ON conditions. If table A has 5 rows and table B has 50 rows and the ON conditions force a match of at most 2 records from table B to each record in table A, the virtual table will have at most 10 rows (not the 250 that would be generated without the ON conditions). Second to be applied is the rest of the WHERE clause that could not be applied during the ON determinations. This is especially true with queries that contain OUTER JOINS. If it didn't happen in this order, we couldn't do an outer join of two tables and look for a null result in the outer table to determine non-matching rows. The third set of filters to be applied comes from the HAVING clause. HAVING conditions are applied after every other portion of the query has been analyzed except for the LIMIT clause. That is why HAVING works on the results of GROUP BY aggregate functions and can use column aliases declared in the SELECT clause. When you put a condition into the ON clause of a JOIN, it is going to be applied to the formation of the virtual table which gets computed _before_ the entire WHERE clause is applied. Under many conditions, some WHERE conditions can also be applied to table JOINs along with the ON restrictions. Luckily, the query optimizer handles that for us. http://dev.mysql.com/doc/mysql/en/LEFT_JOIN_optimization.html http://dev.mysql.com/doc/mysql/en/Where_optimizations.html I am glad you have it working. Come back if you run into any more trouble. Shawn Green Database Administrator Unimin Corporation - Spruce Pine > Man, I thought I had a good handle on OUTER JOINs. Erg. > > Josh > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
