Looks like you got the answer you needed with this problem, but I didn't a
notice an answer to this question, why the following code didn't work, and
was throwing the clientID error. If this has been solved already, pardon my
repetition.
There is a simple typo on the following code: cfset filter
Break it into 2 queries.
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
Rick Faircloth wrote:
Hi, all.
How can I get the same results as this query with using the subselect?
CFQUERY Name=GetWaivers Datasource=#DSN#
Select UW.UtilityWaiverID
from utilitywaivers UW
where Month(UW.IssueDate) = #Form.Month#
and Year(UW.IssueDate) =
Hi, Zac.
I played around a bit with the left outer join before mailing to the list,
but couldn't work out the syntax for this query.
In the original message I did leave out a line which complicates the
query. The original query with the subselect looked like the one below.
The part about
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.
Jochem
Hi, Jochem.
Shouldn't it be RW.ClientID IS NULL
Now, that's a good question. I've always used subselects to get around
having to spend time with those unfriendly (at least to me!) joins.
I don't know enough about the fine points of joinery to answer that.
Suggestions on how to setup the code
Quoting Rick Faircloth [EMAIL PROTECTED]:
Now, that's a good question. I've always used subselects to get
around having to spend time with those unfriendly (at least to me!)
joins.
Then maybe MySQL isn't the database for you :)
Select UW.UtilityWaiverID
from
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...
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
Quoting Jochem..
Then maybe MySQL isn't the database for you :)
You may be right...perhaps I should got back to Access...
at least it was *advanced* enough to handle sub-selects... :o)
Thanks for your help!
You can pick up your tickets for your Hawaiian cruise
at the front desk!
Rick
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) =
These two didn't work out, Stephen.
Never mind
Looks like Jochem's solution did it.
My first one should have been the same as Jochem's just wasn't paying
attention and stuck the RW date checks in the wrong place... :o/
I think the two solutions you suggested didn't work
because they
~lol~ yeah that's it Of course that makes the rest of us that didn't
quite get it right dense too... ;o)
No, the difference is, you hadn't already spent half a day and night trying
to figure out how to code the query! I had! :o)
Rick
-Original Message-
From: Stephen Moretti
Hey, Jochem...
Since you've got a good grip on how the joins work, would my explanation
for my notes concerning why this query works be accurate?
Notes:
This query works to select UtilityWaivers that were issued within a given
month
and which were issued without an accompanying RentalWaiver.
Rick Faircloth wrote:
You may be right...perhaps I should got back to Access...
at least it was *advanced* enough to handle sub-selects... :o)
There are other very nice free databases that have a HISTORY that reads
like MySQL's TODO :-)
Jochem
Rick Faircloth wrote:
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)
Some first form of
I dig PostgreSQL http://www14.us.postgresql.org/ (though I still can't
pronounce it)
(non us www.postgresql.org)
Rob
http://treebeard.sourceforge.net
http://ruinworld.sourceforge.net
Scientia Est Potentia
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]]
Sent:
Rick Faircloth wrote:
Notes:
This query works to select UtilityWaivers that were issued within a given
month and which were issued without an accompanying RentalWaiver.
The part of the query before the where clause selects all UtilityWaivers
which were issued within the specified month
(though I still can't pronounce it)
Pronounce the first section as it looks then add Q-L on the end.
Postgres-Q-L
-Original Message-
From: Rob Rohan [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 2:32 PM
To: CF-Talk
Subject: RE: How to do this without the subselect?
I
Thanks for the overview, Jochem. It's much appreciated.
I'll file the info away for reference.
The where clause then specifies that the joined group should be limited
to those UtilityWaivers whose ClientID does match any RentalWaiver
ClientID.
Yes.
I got one part right! There's hope for me,
20 matches
Mail list logo
