sql rewrite.sql

-- I spent all of yesterday re-writing queries for a government project
-- because their production database runs MySQL v3 and they won't upgrade.
-- MySQL 3 sucks and doesn't even support sub-queries. This is an example of
-- how I wasted what little time I have left:


-- The original:
SELECT p1. * , (
  SELECT COUNT( `id` ) 
  FROM  `pages` p2
   WHERE p1.id = p2.parent_id  ) AS  `children` 
FROM  `pages` p1
   INNER JOIN  `tests` t ON t.id = p1.test_id
   INNER JOIN  `tests_users` tu ON tu.test_id = t.id
WHERE  `parent_id` =5
   AND  `in_menu` =1
   AND  `user_id` =19
ORDER BY `order`

-- Revised:
SELECT p1 . * , COUNT( p2.id ) AS children
FROM `pages` p1
  INNER JOIN  `tests` t ON t.id = p1.test_id
   INNER JOIN  `tests_users` tu ON tu.test_id = t.id
   LEFT JOIN  `pages` p2 ON p2.`parent_id` = p1.`id` 
WHERE p1.`parent_id` =5
  AND p1.`in_menu` =1
  AND  `user_id` =19
GROUP BY p1.`id` 
ORDER BY  `order`