I sometimes will put an AND in my JOIN clause in order to work around that,
something like LEFT JOIN products ON retOrderItems.piProductID =
products.productID <cfif IsDefined('FORM.scpdName') AND FORM.scpdName NEQ 1>
AND (products.productID) = #scPdname#
</cfif>
Although not sure if that works with all databases.
On Tue, Jun 3, 2008 at 6:27 AM, Matt Williams <[EMAIL PROTECTED]> wrote:
> Try this. Note that if you use a where on one of the left joined
> tables it kind of negates the "left" part of that. One way around this
> is to add "or x is null"
>
> FROM retailCustomer
> LEFT JOIN retailOrders ON retailCustomer.retailCustID =
> retailOrders.ocustID
> LEFT JOIN retOrderItems ON retailOrders.orderNo = retOrderItems.piOrderNo
> LEFT JOIN products ON retOrderItems.piProductID = products.productID
> WHERE 0=0
> <cfif IsDefined('FORM.scCounty') AND FORM.scCounty NEQ "">
> AND (retCustCounty) LIKE '%#scCounty#%'</cfif>
> <cfif IsDefined('FORM.scpdName') AND FORM.scpdName NEQ 1>
> AND (products.productID) = #scPdname# OR products.productID IS NULL
> </cfif>
>
>
> On Tue, Jun 3, 2008 at 4:15 AM, Jason Congerton
> <[EMAIL PROTECTED]> wrote:
> > Hi
> >
> > I need to query four tables and return all the results from the
> retailCustomer table regardless of matching records in retailOrders,
> retOrderItems and products. The innner join query i use is below (please
> note i will individually reference items in select stat. once i get this
> right, as well as using query params) I get errors when trying to create the
> left joins, any ideas?
> >
> > Thanks
> >
> > <cfquery name="getCustomers2008" datasource="#application.dsn#">
> > SELECT retailCustomer.*, retailOrders.*, retOrderItems.*, products.*
> > FROM products INNER JOIN ((retailCustomer INNER JOIN retailOrders ON
> retailCustomer.retailCustID = retailOrders.ocustID) INNER JOIN retOrderItems
> ON retailOrders.orderNo = retOrderItems.piOrderNo) ON products.productID =
> retOrderItems.piProductID
> > WHERE 0=0
> > <cfif IsDefined('FORM.scCounty') AND FORM.scCounty NEQ "">
> > AND (retCustCounty) LIKE '%#scCounty#%'</cfif>
> > <cfif IsDefined('FORM.scpdName') AND FORM.scpdName NEQ 1>
> > AND (products.productID) = #scPdname#</cfif>
> > </cfquery>
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306691
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
