SQL LEFT JOIN problem

Question

I'm trying to select the latest updated (field) pages (table), excluding pages that are hidden (visible = "n"). However, I want to group pages by slug so I only get one result per slug.

The following query kinda works, but it only selects the latest updated page for every slug — then, if that page happens to be hidden, it removes it from the results :( In this case I just want to get the latest updated page with that slug that is visible.

SELECT `p1`.`id` AS `pID`, `p1`.`slug` AS `url`, `p1`.`revision`, `p1`.`title`, `p1`.`published`, `p1`.`updated`, (SELECT COUNT(*) FROM `pages` WHERE `slug` = `url` AND `visible` = "y") AS `revisionCount`, (SELECT COUNT(*) FROM `tests` WHERE `pageID` = `pID`) AS `testCount`
FROM `pages` `p1`
LEFT JOIN `pages` `p2` ON `p1`.`slug` = `p2`.`slug` AND `p1`.`updated` < `p2`.`updated`
WHERE `p2`.`updated` IS NULL AND `p1`.`visible` = "y"
ORDER BY `p1`.`updated` DESC

Answer 1

This is the classic case for GROUP BY. And because in your case you don't even want a page considered unless it is 'visible', you are going to use the WHERE clause rather than the HAVING clause.

SELECT MAX(id) as highest_id_for_slug, slug
FROM pages
GROUP BY slug 
WHERE visible = 'y'

You can use this in a subquery, if you like to join these ids to the pages table, or add additional fields - just remember the rule that every column in the SELECT list must be in the GROUP BY, or have an aggregate applied to it.

SELECT * FROM pages WHERE pages.id IN (
  SELECT MAX(id) as highest_id_for_slug
  FROM pages
  GROUP BY slug 
  WHERE visible = 'y'
)

Answer 2

This should work:

SELECT id AS pID, slug AS url, revision, title, published, updated, (SELECT COUNT(*) FROM pages WHERE slug = url AND visible = "y") AS revisionCount, (SELECT COUNT(*) FROM tests WHERE pageID = pID) AS testCount
FROM pages
WHERE updated IN (SELECT MAX(updated) FROM pages WHERE visible = "y" GROUP BY slug) AND visible = "y"
ORDER BY updated DESC

P.S. What’s with all the backticks?