The problem is that you're not joining CasacAppt to CMAppt in any way.
-----Original Message-----
From: Julie Clegg [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 15, 2001 5: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
