Hi all,

I'm trying to modify some code to work on a server with an older version of mySQL (I don't know the exact version number, but I do know it doesn't allow selects within selects). I've played around with it for a while and merely tied my brain in a knot, so I'm hoping that someone can shed some light on this for me.

The code is:

<pre id=code><font face=courier size=2 id=code>
SELECT u.id, u.username,
(SELECT count(*) FROM f_message m WHERE m.createdBy = u.id AND m.createdOn &gt; dateAdd(d, -7, getdate())) as lastweekmessages
FROM f_users u
WHERE u.deleted = 0
ORDER BY lastweekmessages DESC
LIMIT 5
</font id=code></pre id=code>

How can I rewrite this (probably as two queries which I'll have to concatenate) to still get the same data from it?

try this <pre id=code><font face=courier size=2 id=code>select u.id,u.username, count(m.fieldname) from f_users u inner join f_message m on m.createdBy = u.id where m.createdOn &gt; date_sub(curdate(), interval 7 day) and u.deleted =0 Limit 5</font id=code></pre id=code>

Ah, that seems to work just fine. I'm still a little green at doing complicated things in SQL (anything more complicated than basic selects, inserts and deletes).

So I've looked at what you did there, and tried to apply it to the following query:
<pre id=code><font face=courier size=2 id=code>
SELECT u.id, u.username,
(select count(*) from #dbprefix#blogEntry be where be.userID = u.id and createdOn &gt; #dateAdd("m", -1, now())#) as blogs
FROM #dbprefix#users u
WHERE (select count(*) from #dbprefix#blogEntry be where be.userID = u.id and createdOn &gt; #dateAdd("m", -1, now())#) &gt; 0
ORDER BY blogs DESC
LIMIT 5
</font id=code></pre id=code>

I came up with the following:
<pre id=code><font face=courier size=2 id=code>
SELECT u.id, u.username, count(be.userID) AS blogs
FROM #dbprefix#users u INNER JOIN #dbprefix#blogEntry be ON (be.userID = u.id and createdOn &gt; #dateAdd("m", -1, now())#)
WHERE blogs &gt; 0
ORDER BY blogs DESC
LIMIT 5
</font id=code></pre id=code>

However, this gives me the error:
<b>Error Executing Database Query.
Column not found: Unknown column 'blogs' in 'where clause'</b>

I can understand why, I think, but is there a way around this?

Sorry mate but your query is wrong you haven't select any table at all. this line
<pre id=code><font face=courier size=2 id=code>#dbprefix#users u INNER JOIN #dbprefix#blogEntry be ON (be.userID = u.id and createdOn &gt; #dateAdd("m", -1, now())#)</font id=code></pre id=code> is a comment so there's no table present.

Why is it a comment? Because of the #? These don't actually get sent to the database, they are CF code to access a variable. I've taken them out anyway, for now, and also changed the variable function for getting the date to something based of the one given to me in the initial query. So what we have now is:

<pre id=code><font face=courier size=2 id=code>
SELECT u.id, u.username, count(be.userID) AS blogs
FROM f_users u INNER JOIN f_blogEntry be ON (be.userID = u.id and createdOn &gt; date_sub(curdate(), interval 1 month))
WHERE blogs &gt; 0
ORDER BY blogs DESC
LIMIT 5
</font id=code></pre id=code>

No idea if that date_sub part is right, but in any event I'm still getting the error that "blogs" in the WHERE clause is an unknown column.

Sorry didn't know you were using this with coldfusion.
Ok now the query should work. Just replace what you need to replace with variables.
<pre id=code><font face=courier size=2 id=code>
SELECT u.id, u.username, count(be.userID) AS blogs
FROM f_users u INNER JOIN f_blogEntry be ON be.userID = u.id WHERE createdOn &gt; date_sub(curdate(), interval 1 month)
having blogs &gt; 0
ORDER BY blogs DESC
LIMIT 5
</font id=code></pre id=code>

Ah, that's better! I've had to play around with it a bit (it seems to want "group" if I'm going to use "count" but I came up with:

<pre id=code><font face=courier size=2 id=code>
SELECT u.id, u.username, count(be.userID) AS blogs
FROM f_users u INNER JOIN f_blogEntry be ON be.userID = u.id
WHERE be.createdOn &gt; #dateAdd("m", -1, now())#
GROUP BY u.username DESC
HAVING blogs &gt; 0
ORDER BY blogs DESC
LIMIT 5
</font id=code></pre id=code>

And this seems to work just fine. Thanks for your help. <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>