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:306687
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
