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
