Sounds like a "LEFT Join" is in order.
- BILL -
-----Original Message-----
From: Amanda Stern [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 21, 2001 1:16 PM
To: CF-Talk
Subject: Cf in Select Stmt help
Hello,
I am trying to get this query to work and not sure
how...anyone have any ideas? The problem is in the
joining of the cas table and the cats table....there
is a 0 to many relationship between these
tables....ie[cas.iCasacApptID = cats.iCasacApptID]
many never equal each other because there may not be
an iCasacApptID in the cats table. If thats the case
then I want the word "None" as my cats.cTreatSelectID.
is there a way I can put a cfif statement around that
join..if so how..or is there a simplier way I am not
thinking of
Thanks again for the help!
<cfquery name="QRYtp"
datasource="#request.projectAceDb#">
<cfloop query="treatCount">
<cfif treatcount.TreatCount gt 6>
Select cas.iClientID ,
cd1.vCodeDecode as TreatSelect,
'#treatCount.TreatProg#' as TrueProg,
'#treatCount.HRACode#' as TrueHRA
>From CasacAppt cas,
CasacApptTreatSelect cats,
TreatmentProg tp,
CodeDecode cd1,
Client cl
Where cas.iClientID = cl.iClientID
AND '#treatCount.TreatProgID#' = cas.iTreatProgID
AND cas.iCasacApptID = cats.iCasacApptID
AND cats.cTreatSelectID = cd1.icodedecodeID
AND cas.dCasacDate >=
'#variables.stFieldNames.DBeginDate#'
AND cas.dCasacDate <=
'#variables.stFieldNames.dEndDate#'
AND cas.dCasacDate > '#request.dContractDate#'
</cfif>
</cfloop>
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
