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 and which have
> accompanying RentalWaivers with matching ClientID's for the same month.
I would say:
The FROM clause selects all rows (LEFT JOIN) from UtilityWaivers, then
checks if there is a row in RentalWaivers that matches the conditions
<equal clientID>, <same month>, <same year> and if so appends the fields
from it to the rows just selected. If not, the fields are still appended
to have the correct number of fields, but filled out with NULL's.
> The "where" clause then specifies that the joined group should be limited
> to those UtilityWaivers whose ClientID does match any RentalWaiver ClientID.
Yes.
General strategy for building queries which works best for *me*:
- first FROM including all joins
- then WHERE
- then SELECT
- then GROUP BY, ORDER BY
Generalized strategy for flattening a query with subqueries:
SELECT *
FROM a
WHERE a.field IN (SELECT b.field FROM b WHERE <subquery predicates>)
AND <mainquery predicates>
SELECT a.*
FROM a <something> JOIN b
ON (a.field = b.field AND <subquery predicates>)
WHERE <mainquery predicates>
If IN is actually NOT IN, <something> becomes LEFT and you add the NOT
NULL to the predicates.
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
