Ouch.. my sql was incomplete, and the max() function is not working as I'd
have expected it... so it's work-around time...
SELECT
cas.iCasacApptID, cas.cEncCatID
FROM
CMMonthlyContact con INNER JOIN
(CMAppt cm INNER JOIN
(CasacApptRelationship rel INNER JOIN
CasacAppt cas ON rel.iCasacApptID = cas.iCasacApptID)
ON rel.iCMApptID = cm.iCMApptID)
ON con.iEmpID = cm.iCMApptID
GROUP BY
cas.iCasacApptID, cas.cEncCatID, cm.bActive
HAVING
cas.iCasacApptID in
(SELECT max(rel2.iCasacApptID)
FROM CasacApptRelationship rel2
WHERE rel2.iCMApptID = cm.iCMApptID)
AND cm.bActive = 1
----- Original Message -----
From: "another programmer" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, May 16, 2001 11:34 AM
Subject: Re: SQL Newbie - Help!!!!! (well, I'm answering part 2, though this
was originally an answer to part 1... hey! what a long title!)
> "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
