Nested Query

teclo · March 21st, 2005, 02:54 PM

I have a slight problem, I am converting a system from MS SQL to MySQL and there is a query that is giving me gip.

select log.*, comment_count = (select count(*) from ucomments where ucomments.logid = log.logid) from log order by eventtime desc

Basically, this just lists a table of log entries, and but also gives me the count from another table that is linked to this table. Not sure if I am describing it correctly. In essence it should list like this:

"Yes that worked", 2 comments
"Good morning", 0 comments
"Did you see this", 5 comments

Anyway, this works perfectly in MSSQL 2000, but trips up mySQL, any ideas? I have looked about for a while but can't find anything useful.

Any help would be greatly appreciated, cheers.

ChrisScott · March 22nd, 2005, 07:20 AM

AFAIK MySQL still does not support sub queries, so you'll have to find another way to get the data you want.

This (untested), should do what you're after...

Code:

SELECT log.*, COUNT(ucomments.logid) AS comment_count
FROM log LEFT JOIN ucomments ON ucomments.logid = log.logid
GROUP BY log.logid
ORDER BY log.eventtime DESC;

HTH,

Chris