Still don't know why Stas' idea didn't work with the two queries
and a valuelist. Ideas? Kept getting the error that CF couldn't
define ClientID in the cfset statement...
Rick
<CFQUERY Name="GetWaivers1" Datasource="#DSN#">
Select RW.ClientID
from RentalWaivers RW
where Month(RW.RentalWaiverDate) = #Form.Month#
and Year(RW.RentalWaiverDate) = #Form.Year#)
</CFQUERY>
<cfset filter = valuelist(getWaivers1,clientID)>
<CFQUERY Name="GetWaivers" Datasource="#DSN#">
Select UW.UtilityWaiverID
from utilitywaivers UW
where Month(UW.IssueDate) = #Form.Month#
and Year(UW.IssueDate) = #Form.Year#
and UW.ClientID not in (#filter#)
</CFQUERY>
-----Original Message-----
From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 11:54 AM
To: CF-Talk
Subject: RE: How to do this without the subselect?
These two didn't work out, Stephen.
Looks like Jochem's solution did it.
I think the two solutions you suggested didn't work
because they both required that the
RentalWaiverDate's had to match the Form dates.
Because the only hits in the query would be those
that did have matching RentalWaiver dates, that caused nothing
to be returned even though there were 3 records that should
have been returned for the test date.
It would return the correct hits only when there was
a RentalWaiver for the Form dates and the ClientID's didn't match.
If there was not RentalWaiver for the Form dates, nothing was returned.
I tried coding various scenarios within the query,
i.e., UW.ClientID=RW.Client, but RW.WaiverIssueDate <> Form Dates
or UW.ClientID<>RW.Client, but RW.WaiverIssueDate = Form Dates,
basically trying to cover all possibilities, but even then results weren't
correct.
*Unfortunately*, the totally *unintuitive* (for me), but *accurate* left
join from
Jochem worked. (Thanks, Jochem. :o)
I've just always found the joins, especially left, outer, inner, etc., to be
unintuitive when looking at the code. I guess I just haven't worked with
them enough.
I've always worked around them with subselects, which mySQL won't do. (Until
4.2)
Or perhaps I'm too dense! :o)
Thanks for your help.
Rick
-----Original Message-----
From: Stephen Moretti [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 10:25 AM
To: CF-Talk
Subject: Re: How to do this without the subselect?
> Quoting Rick Faircloth <[EMAIL PROTECTED]>:
> >
> > Since the where statement is taken by the "#MultipleWaivers..." line,
> > it doesn't leave room for another where statement such as
> > where UW.ClientID is NULL...
>
> Shouldn't it be RW.ClientID IS NULL.
>
You're probably right....
Two possibles... (Neither tested, so expect typo's etc.)
One is :
SELECT UW.UtilityWaiverID
FROM utilitywaivers AS UW RIGHT JOIN RentalWaiver AS RW
ON UW.ClientID = RW.ClientID
WHERE RW.ClientID IS NULL
AND UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]#
AND Month(UW.IssueDate) = #Form.Month#
AND Year(UW.IssueDate) = #Form.Year#
AND Month(RW.RentalWaiverDate) = #Form.Month#
AND Year(RW.RentalWaiverDate) = #Form.Year#)
The other is :
SELECT UW.UtilityWaiverID
FROM UtilityWaiver AS UW, RentalWaiver AS RW
WHERE UW.ClientID <> RW.ClientID
AND UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]#
AND Month(UW.IssueDate) = #Form.Month#
AND Year(UW.IssueDate) = #Form.Year#
AND Month(RW.RentalWaiverDate) = #Form.Month#
AND Year(RW.RentalWaiverDate) = #Form.Year#)
I have a sneaky suspicious that the second query won't work, but give them a
go and let us know....
Regards
Stephen
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
