I don't have your DB so I can't test this, but it's close. SELECT l.id, l.description,l.build_date,l.edit_date,l.admin_user, COUNT(ll.id) AS userCount FROM mailroomLead l LEFT OUTER JOIN (mailRoomLeadList ll INNER JOIN customer c ON ll.customerid = c.id) ON l.id = ll.id) WHERE c.mail_list <> 0 GROUP BY l.id, l.description,l.build_date,l.edit_date,l.admin_user ORDER BY description DESC
+-----------------------------------------------+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst TeleCommunication Systems [EMAIL PROTECTED] +-----------------------------------------------+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis "Let's Roll" - Todd Beamer, Flight 93 -----Original Message----- From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 03, 2002 1:36 PM To: CF-Talk Subject: OT: SQL without Subquery I have the following query that I am trying to use on a mySQL db (which doesn't support sub-queries). Can anyone see a good way to write this query other than with the sub query? SELECT l.id, l.description,l.build_date,l.edit_date,l.admin_user, (SELECT COUNT(*) FROM mailroomLeadList ll INNER JOIN customer c ON ll.customerid = c.id WHERE l.id = ll.id AND c.mail_list <> 0 ) AS userCount FROM mailroomLead l ORDER BY description DESC I was trying to stick this information into a 'temp' table via mySQL, but it is producing errors on the COUNT(*) ... Any suggestions? Thank you Paul Giesenhagen QuillDesign ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com