inner joins are eqivalent to a WHERE join , dont use join unless you are doing OUTERs .
you should also consider creating a VIEW in the db so you dont have to do all the joins for every query.
Pat
On 5/5/06, Seona Bellamy <[EMAIL PROTECTED]> wrote:
Hi guys,
I've inherited an extremely complex query for our site's classified listings. I've managed to make it slightly less complicated since I've changed the table structure in the database somewhat - I felt it somewhat unnecessary to have the data divided into quite as many tables as they had.
Sadly, however, the query is now giving me errors. This is the query in all its ghastly glory:
---------------------------------------------------------------------------------------------------------------
<cfquery name="#tmpQ#" datasource="#application.misc.dsn#">
SELECT DISTINCT cl.ID, cl.TradingAs, "groupA" as ordertype, "<cfif len(form.postcode) and isnumeric(form.postcode)>Outside your area:</cfif>" as area
FROM (((((category c INNER JOIN (classifieds cl INNER JOIN c_classifiedHasCategory chc ON cl.ID = chc.classifiedID) ON c.categoryID = chc.categoryID) INNER JOIN c_classifiedStatus cs ON cl.statusID = cs.statusID) INNER JOIN c_classifiedType ct ON cl.classifiedTypeID = ct.classifiedTypeID) INNER JOIN c_classifiedHasRegion chr ON cl.ID = chr.classifiedID) INNER JOIN c_classifiedRegion cr ON chr.regionID = cr.regionID) INNER JOIN c_classifiedRegionHasBSP r2b ON cr.regionID = r2b.regionID) INNER JOIN postcoderaw pcr ON r2b.BSPName = pcr.BSPName
WHERE cl.expiryDate >= <cfqueryparam value="#form.datefilter#" cfsqltype="cf_sql_date">
AND cs.statusID=<cfqueryparam value="#application.uuid.status.classified.active#" cfsqltype="cf_sql_char" maxlength="35">
<cfif len(form.category)>AND c.categoryID in (#listqualify(form.category,"'",",","all")#)</cfif>
<cfif len(form.postcode)>
AND pcr.Pcode <> <cfqueryparam value="#form.postcode#" cfsqltype="cf_sql_integer" maxlength="4">
<cfif len(inThisArea)>AND cl.ID not in (#listqualify(inThisArea,"'",",","all")#)</cfif>
</cfif>
AND ct.classifiedTypeID in (#listqualify("#application.uuid.classifiedtype.platinum#","'",",","all")#)
UNION
SELECT DISTINCT cl.classifiedID, cl.TradingAs, "groupB" as ordertype, "<cfif len(form.postcode)>Outside your area:</cfif>" as area
FROM (((((category c INNER JOIN (classifieds cl INNER JOIN c_classifiedHasCategory chc ON cl.ID = chc.classifiedID) ON c.categoryID = chc.categoryID) INNER JOIN c_classifiedStatus cs ON cl.statusID = cs.statusID) INNER JOIN c_classifiedType ct ON cl.classifiedTypeID = ct.classifiedTypeID) INNER JOIN c_classifiedHasRegion chr ON cl.ID = chr.classifiedID) INNER JOIN c_classifiedRegion cr ON chr.regionID = cr.regionID) INNER JOIN c_classifiedRegionHasBSP r2b ON cr.regionID = r2b.regionID) INNER JOIN postcoderaw pcr ON r2b.BSPName = pcr.BSPName
WHERE cl.expiryDate >= <cfqueryparam value="#form.datefilter#" cfsqltype="cf_sql_date">
AND cs.statusID=<cfqueryparam value="#application.uuid.status.classified.active#" cfsqltype="cf_sql_char" maxlength="35">
<cfif len(form.category)>AND c.categoryID in (#listqualify(form.category,"'",",","all")#)</cfif>
<cfif len(form.postcode)>
AND pcr.Pcode <> <cfqueryparam value="#form.postcode#" cfsqltype="cf_sql_integer" maxlength="4">
<cfif len(inThisArea)>AND cl.ID not in (#listqualify(inThisArea,"'",",","all")#)</cfif>
</cfif>
AND ct.classifiedTypeID in (#listqualify("#application.uuid.classifiedtype.premium#,#application.uuid.classifiedtype.standard#","'",",","all")#)
order by area, ordertype, tradingas
</cfquery>
-----------------------------------------------------------------------------------------
I'm getting the following error from it:
Error Executing Database Query. Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ') INNER JOIN postcoderaw pcr ON r2b.BSPName = pcr.BSPName <br>The error occurred on line 59.
Now, line 59 is actually not the line with all the joins on it. Line 59 is the final "AND ct.classifiedTypeID in...." and so is several lines further on. This has me completely stumped. I can't find anything wrong with either the line that they call by number or the line that they call by content.
Can anyone else suggest what may be the problem here?
Cheers,
Seona.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "cfaussie" group.
To post to this group, send email to cfaussie@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cfaussie
-~----------~----~----~----~------~----~------~--~---
