RE: How to do this without the subselect?

[Rick Faircloth]

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.

The part of the query before the "where" clause selects all UtilityWaivers
which were issued within the specified month and which have
accompanying RentalWaivers with matching ClientID's for the same month.

The "where" clause then specifies that the joined group should be limited
to those UtilityWaivers whose ClientID does match any RentalWaiver ClientID.

     <CFQUERY Name="GetUCSWCount" Datasource="#DSN#">
          Select UW.UtilityWaiverID
            from utilitywaivers UW
       left join RentalWaivers RW
               on (UW.ClientID = RW.ClientID
             and Month(RW.RentalWaiverDate) = #Form.Month#
             and Year(RW.RentalWaiverDate) = #Form.Year#
                      )
           where UW.UtilityCompanyID = #MultipleWaivers[CurrentRow][1]#
             and Month(UW.IssueDate) = #Form.Month#
             and Year(UW.IssueDate) = #Form.Year#
             and RW.ClientID IS NULL
     </CFQUERY>

Thanks,

Rick



-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 10:21 AM
To: CF-Talk
Subject: RE: How to do this without the subselect?


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 utilitywaivers UW
>        where UW.UtilityCompanyID=#MultipleWaivers[CurrentRow][1]#
>           and Month(UW.IssueDate) = #Form.Month#
>           and Year(UW.IssueDate) = #Form.Year#
>           and UW.ClientID not in
>                 (Select RW.ClientID from RentalWaivers RW
>                  where Month(RW.RentalWaiverDate) = #Form.Month#
>                     and Year(RW.RentalWaiverDate) = #Form.Year#)

I think it should be:

SELECT UW.UtilityWaiverID
FROM   utilitywaivers UW LEFT JOIN RentalWaivers RW
           ON (UW.ClientID = RW.ClientID
              AND Month(RW.RentalWaiverDate) = #Form.Month#
              AND Year(RW.RentalWaiverDate) = #Form.Year#
              )
WHERE  UW.UtilityCompanyID = #MultipleWaivers[CurrentRow][1]#
       AND Month(UW.IssueDate) = #Form.Month#
       AND Year(UW.IssueDate) = #Form.Year#
       AND RW.ClientID IS NULL

Don't forget to add the appropriate cfqueryparam tags.

Jochem

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm