Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
|
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 21st, 2005, 02:54 PM
Registered User
 
Join Date: Mar 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Nested Query

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.

 
Old March 22nd, 2005, 07:20 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested query deb1980 ASP.NET 2.0 Basics 2 April 10th, 2007 04:38 AM
Nested Query Help Needed Corey Access 2 April 26th, 2006 02:17 PM
Nested Query or Join? rlull SQL Server 2000 4 November 10th, 2005 05:23 PM
Nested Query BrianWren Oracle 5 February 1st, 2005 01:45 PM
Nested Query Trouble ashley_y ADO.NET 2 December 23rd, 2003 01:32 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.