Translating an Aggregate, Outer Join Query to ANSI-92

Question

Executive summary: A query that was composed under SQL ANSI-89 needs to be transferred to a database that using SQL ANSI-92. Outer joins are involved.

---

I've been running an ANSI-89 query

I can give the table structures if needed, but the basic idea is that we have a table of employees, a table of online courses, and an intermediate "employee course taken" table that has rows of an employee id, a course id, and a rating column that we're using as a pass/fail indicator.

Our table style for key columns is that primary keys are always 'id', while foreign keys are always 'tablename_id'.

Here's the ANSI-89 (MySQL 4.1.14) version of the select:

SELECT E.id,
   E.firstname, E.surname, E.suffix,
   sum( if( ECT.rating =1, 1, 0 ) ) AS passcount,
   sum( if( ECT.rating =0, 1, 0 ) ) AS retakecount,
   sum( if( ECT.rating IS NULL , 1, 0 ) ) AS totakecount,
   L.locationname
FROM employee E,
   location L
INNER JOIN emplcoursetaken ECT ON E.id = ECT.employee_id
RIGHT JOIN courses C ON C.id = ECT.course_id
WHERE C.linkready =1
AND E.location_id = L.id
GROUP BY E.id
ORDER BY L.locationname, E.surname, E.firstname;

And it works. I get a nice set of rows like this one:

620 Johnny  Test        12  0   14  Chicago

Johnny has taken 12 out of 26 courses, and passed those twelve.

But I'm having trouble moving this query to ANSI-92 (MySQL 5.1.55).

SELECT E.id,
   E.firstname, E.surname, E.suffix,
   sum( if( ECT.rating =1, 1, 0 ) ) AS passcount,
   sum( if( ECT.rating =0, 1, 0 ) ) AS retakecount,
   sum( if( ECT.rating IS NULL , 1, 0 ) ) AS totakecount,
   L.locationname
FROM employee E
INNER JOIN emplcoursetaken ECT ON E.id = ECT.employee_id
RIGHT JOIN courses C ON C.id = ECT.course_id
INNER JOIN location L ON E.location_id = L.id
WHERE C.linkready =1
GROUP BY E.id
ORDER BY L.locationname, E.surname, E.firstname;

There are only nine courses in this database's courses table, so I should get a different non-zero "totakecount" value:

620 Johnny  Test    NULL    1   0   0   Chicago 

There should be an '8' in the totakecount column. Instead there's a zero. As an experiment I changed the "RIGHT" to an "INNER", and got the same result. So I'm clearly doing something wrong with my syntax, but my web searching isn't telling me what.

Thanks.

Answer 1

I am not sure that your original query is ANSI 89. However, it is using a , as a cross join. Just replace it with a cross join:

SELECT E.id,
   E.firstname, E.surname, E.suffix,
   sum( if( ECT.rating =1, 1, 0 ) ) AS passcount,
   sum( if( ECT.rating =0, 1, 0 ) ) AS retakecount,
   sum( if( ECT.rating IS NULL , 1, 0 ) ) AS totakecount,
   L.locationname
FROM employee E cross join
   location L
INNER JOIN emplcoursetaken ECT ON E.id = ECT.employee_id
RIGHT JOIN courses C ON C.id = ECT.course_id
WHERE C.linkready =1
AND E.location_id = L.id
GROUP BY E.id
ORDER BY L.locationname, E.surname, E.firstname;

From the looks of it, you can then convert the cross join to an inner join:

SELECT E.id,
   E.firstname, E.surname, E.suffix,
   sum( if( ECT.rating =1, 1, 0 ) ) AS passcount,
   sum( if( ECT.rating =0, 1, 0 ) ) AS retakecount,
   sum( if( ECT.rating IS NULL , 1, 0 ) ) AS totakecount,
   L.locationname
FROM employee E join
   location L
   on E.location_id = L.id
INNER JOIN emplcoursetaken ECT ON E.id = ECT.employee_id
RIGHT JOIN courses C ON C.id = ECT.course_id
WHERE C.linkready =1
GROUP BY E.id
ORDER BY L.locationname, E.surname, E.firstname;

Your comment is interesting. I thought cross join and , were semantically identical. This is true historically, but not now (as explained here):

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

If this is the case, then parentheses should fix the precedence problem:

FROM (employee E cross join
      location L
     ) el
    INNER JOIN emplcoursetaken ECT ON El.id = ECT.employee_id

The only issue is the aliasing issue . . . you may need to change them to el.

Answer 2

Tested on MySQL 5.5.30:

SELECT E.id,
   E.firstname, E.surname, E.suffix,
   sum( if( ECT.rating =1, 1, 0 ) ) AS passcount,
   sum( if( ECT.rating =0, 1, 0 ) ) AS retakecount,
   sum( if( ECT.rating IS NULL , 1, 0 ) ) AS totakecount,
   L.locationname
FROM
courses C 
CROSS JOIN employee E
INNER JOIN location L ON E.location_id = L.id
LEFT JOIN emplcoursetaken ECT ON E.id = ECT.employee_id AND C.id = ECT.course_id
WHERE C.linkready =1
GROUP BY E.id
ORDER BY L.locationname, E.surname, E.firstname;

+----+-----------+---------+--------+-----------+-------------+-------------+--------------+
| id | firstname | surname | suffix | passcount | retakecount | totakecount | locationname |
+----+-----------+---------+--------+-----------+-------------+-------------+--------------+
|  1 | Johnny    | Test    | NULL   |         8 |           0 |           1 | Chicago      |
+----+-----------+---------+--------+-----------+-------------+-------------+--------------+