"What do I change to make it grab the max CasacApptID PER iCMApptID"
Well, that'd require you connecting the CMAppt table and the CasacAppt
table somehow... I'm guessing CasacApptRelationship is meant to be the table
which joins the two (CMAppt & CasacAppt)?
SELECT cas.iCasacApptID, cas.cEncCatID
FROM
CMMonthlyContact con INNER JOIN
(CMAppt cm INNER JOIN
(CasacApptRelationship rel INNER JOIN CasacAppt cas ON
rel.iCasacApptID)
ON rel.iCMApptID = cm.iCMApptID)
ON con.iEmpID = cm.iCMApptID,
GROUP BY cas.iCasacApptID, cas.cEncCatID, cm.bActive
HAVING cas.iCasacApptID = Max(rel.iCasacApptID)
AND cm.bActive = 1
though note, I've removed Client cli because you don't use it at all
----- Original Message -----
From: "Julie Clegg" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, May 16, 2001 8:52 AM
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
