someone has copied and pasted that query from Microsoft Access originally.. it
uses brackets.. or maybe Crystal Reports was used to generate it.
I have never seen anyone type them themselves.
>>> [EMAIL PROTECTED] 5/05/2006 9:58 am >>>
get rid of the brackets on the inner join, from what i can tell they are
doing sweet FA.
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
-~----------~----~----~----~------~----~------~--~---
