i think you need more joins between your tables.
Alexander Sicular
Chief Technology Architect
The Neurological Institute of New York
Columbia University
212.305.1318
as867 {at} columbia [dot] edu
> -----Original Message-----
> From: Julie Clegg [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, May 15, 2001 8:16 PM
> To: CF-Talk
> Subject: Re: SQL Newbie - Help!!!!! part 2
>
>
>
> okay I actually got the problem below taken care of but its
> not spitting out
> what I want...I changed the query to read:
>
> Select cas.iCasacApptID
> , cas.cEncCatID
> From CasacAppt cas
> , Client cli
> , CasacApptRelationship rel
> , CMMonthlyContact con
> , CMAppt cm
> Where cas.iCasacApptID = (Select Max(rel.iCasacApptID)
> From
> CasacApptRelationship
> rel)
> AND con.iEmpID = cm.iCMApptID
> AND cm.bActive = 1
> Group By rel.iCMApptID, cas.iCasacApptID, cas.cEncCatID
>
>
> this is outputting three dupicate records....the one with the max
> iCasacApptID. I want it to output the max icasacApptID per
> iCMApptID...I
> thought by putting the rel.iCMApptID in the Group By clause
> it would take
> care of it but it doesnt. There are a total of 3 unique
> iCMApptID's and
> this query is spits out:
>
> 22 8
> 22 8
> 22 8
>
>
> What do I change to make it grab the max CasacApptID PER iCMApptID
>
> TIA!!!!!!!
>
> Julie
>
> >From: "Julie Clegg" <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: CF-Talk <[EMAIL PROTECTED]>
> >Subject: SQL Newbie - Help!!!!!
> >Date: Tue, 15 May 2001 17:52:30 -0500
> >
> >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
