Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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`
Add Comment
Please, Sign In to add comment