Try this:
Select cas.iCasacApptID, cas.cEncCatID
>From CasacAppt cas, CMMonthlyContact con, CMAppt cm
Where cas.iCasacApptID = (Select Max(iCasacApptID) from
CasacApptRelationship)
AND con.iEmpID = cm.iCMApptID
AND cm.bActive = 1
Group By cas.iCasacApptID, cas.cEncCatID
You don't need the Client table because you are not referencing it. This
may not give you exactly what you want because you're not joining CasacAppt
with CMMonthlyContact in any way.
-----Original Message-----
From: Julie Clegg [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 15, 2001 3:53 PM
To: CF-Talk
Subject: SQL Newbie - Help!!!!!
Select cas.iCasacApptID, cas.cEncCatID
>From CasacAppt cas, Client cli, CasacApptRelationship rel, CMMonthlyContact
con, CMAppt cm
Where cas.iCasacApptID = (Select Max(rel.iCasacApptID) )
AND con.iEmpID = cm.iCMApptID
AND cm.bActive = 1
Group By cas.iCasacApptID, cas.cEncCatID
I keep getting an error saying: An aggregate may not appear in the WHERE
clause unless it is in a subquery contained in a HAVING clause or a select
list, and the column being aggregated is an outer reference.
Am I getting the syntax wrong or is this query not possible...I've played
around with it a lot and cnat get it to work.
Any suggestions?
Thanks,
Julie
>From: "John" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: CF-Talk <[EMAIL PROTECTED]>
>Subject: RE: SQL Newbie - Help!!!!!
>Date: Tue, 15 May 2001 14:07:28 -0500
>
>I think this will give you what you want.
>
>select top 1 t2.pk_CasacAppt from tblCasacCM left outer join tblCasac t2
>on t1.pk_CasacAppt = t2.pk_CasacAppt
>left outer join tblContacts t3 on t1.pk_CMAppt = t3.CMAppt
>where pk_uniqueID = #Variable#
>order by t2.CasacDate desc
>
>John Hancock
>
>-----Original Message-----
>From: Julie Clegg [mailto:[EMAIL PROTECTED]]
>Sent: Tuesday, May 15, 2001 1:01 PM
>To: CF-Talk
>Subject: SQL Newbie - Help!!!!!
>
>
>Hello,
>
>I have a large sql query I need to write and am having some problems with
>it. I have broken it down into several sections...the fist section I
>cannot
>figue out involves 3 table which I have listed below along with the main
>fields I need to deal with in parenthasis:
>
>tblContacts
>(pk_uniqueID, CMAppt)
>
>tblCasacCM
>(pk_CMAppt, pk_CasacAppt)
>
>tblCasac
>(pk_CasacAppt, CasacDate)
>
>
>In the tblCasac, there are many pk_CasacAppt's that relate to only one
>CMAppt. I need to capture the most recent pk_CasacAppt(using CasacDate)
>for
>a particular CMAppt.
>Hope this all makes sense,,,any suggestions would be greatly appreciated...
>
>
>TIA,
>
>Julie
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
