Well it was such an appalling mess, I just reverted back! :lol:
I was trying to merge the parts of the original query (posted previously) with your own.
I'll be honest, all of this way beyond me — the queries I write are, for the most part, very basic.
bookmarks.id, source.duplicates, GROUP_CONCAT(links.user_id) AS users, links.user_id, bookmarks.url, bookmarks.title, SUBSTR(bookmarks.snippet, 1, 100) as snippet, GROUP_CONCAT(tags.tag) AS tags, bookmarks.datetime, links.status
SELECT url, COUNT(*) AS duplicates, MIN(datetime) AS earliest
FROM bookmarks GROUP BY url) AS source, links, tags
INNER JOIN bookmarks ON (bookmarks.url = source.url)
AND (bookmarks.datetime = source.earliest)
AND (links.status = 'public')
AND (links.bookmark_id = bookmarks.id) GROUP BY bookmarks.url
ORDER BY bookmarks.datetime DESC LIMIT 0, 9
All of which gives me an error:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON (bookmarks.url = source.url) AND (bookmarks.datetime = source.earliest) AND (' at line 1
From what I can grasp, MySQL doesn't know to handle the various references to columns belonging to the links and tags tables, and I don't know where to put the table names.