--- Phill Sparks <[EMAIL PROTECTED]> wrote:
> Hi Group,
>
> I've just moved hosts and sadly they're running MySQL 4.1.22 and a
> query running on my previous host (MySQL 5.0.45) has stopped
> working...
>
> I've got a nice nested query (see http://www.milk-hub.net/pb/08 ) that
> gets related content based on tags; the outer query collates the uri
> (like an ID) and tags to get the best 5 matches. Can anyone suggest a
> way of running this on the new server, preferably all in one query. I
> could out source the outer query to PHP but the SQL here is much
> cleaner and returns only the results I need.
>
> Cheers,
>
> Phill
>
> ------------------------------------
>
Looking at your query:
SELECT uri, COUNT(tag) AS related_tag_count
FROM (
SELECT uri, tag
FROM milk_blog_tags prop
WHERE uri <> 'posts/2008/01/17/finding_the_character_map_in_mac'
AND tag IN ('software','Character Map','Character Palette','for:lsucs','osx')
) AS tags
GROUP BY uri
HAVING COUNT(tag) > 0
ORDER BY COUNT(tag) DESC, uri DESC
LIMIT 0, 5
I wonder why you are using a subquery to supply values for the FROM part of
your clause. Are you consulting different tables? I've not seen it done this
way and I'm surprised it would work on any version since you are selecting two
columns in the subquery.
It would be helpful if you told us exactly what error was being returned by
MySQL.
James