if I understand what you're saying, this should give you what you want in one query...
select loopip, rec_num, lastvisit, count(loopip) as loop_count from tablename where loopip in (#listqualify(listofips, "'")#) group by loopip -----Original Message----- From: Keith Purtell [mailto:[EMAIL PROTECTED]] Sent: Monday, January 07, 2002 12:20 PM To: KCFusion (E-mail) Subject: [KCFusion] Retrieving alias from SQL sub-query Slightly off topic, but I'm having difficulty retrieving an alias from a sub-query. I start with a list of IP addresses, then loop through the list with this query: SELECT loopIP, rec_num, lastvisit, loop_count FROM TableName WHERE loopIP = '#ListElement#' AND NOT loop_count = 0 AND EXISTS (SELECT COUNT(loopIP) AS Count_IP FROM TableName WHERE loopIP = '#ListElement#' AND NOT loop_count = 0 GROUP BY loopIP) My goal is to CFOUTPUT table rows including one column that shows how many times the IP address in that record appears in the database. But when I try this I get error messages saying the server doesn't recognize Count_IP (my alias for the count). The SQL runs fine; the error occurs when CFOUTPUT is attempted. Any tips? TIA. Keith Purtell, Web/Network Administrator VantageMed Operations (Kansas City) Email: [EMAIL PROTECTED] CONFIDENTIALITY NOTICE: This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ______________________________________________________________________ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe.................... mailto:[EMAIL PROTECTED] To Unsubscribe................ mailto:[EMAIL PROTECTED] ______________________________________________________________________ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe.................... mailto:[EMAIL PROTECTED] To Unsubscribe................ mailto:[EMAIL PROTECTED]
