Link to home
Create AccountLog in
Avatar of 14_east
14_eastFlag for United States of America

asked on

SQL syntax help/error

I hav a query which is throwing an error, and I cannot seem to get it cleared.  The query is attached, and the error is below:

[Error Executing Database Query. 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 'LEFT JOIN tbl_prdtcat_rel pc ON p.product_ID = pc.prdt_cat_rel_Product_ID WH' at line 3
The error occurred on line 36.]
 
<cfquery name="rsProductsSearch" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
SELECT DISTINCT p.product_ID, p.product_Sort, p.product_Name, p.product_MerchantProductID
FROM 
	LEFT JOIN tbl_prdtcat_rel pc
	ON p.product_ID = pc.prdt_cat_rel_Product_ID 
WHERE 
	p.product_OnWeb = 1
	AND p.product_Archive = 0
	<cfif application.allowbackorders EQ 0>
		AND s.SKU_Stock > 0
	</cfif>
		AND pc.prdt_cat_rel_Cat_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.c#">
	AND s.SKU_ShowWeb = 1
	ORDER BY
		p.product_Sort, 
		p.product_Name
</cfquery>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Armand G
Armand G
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
you have the issue with FROM and LEFT JOIN keywords with the query.

Please refer the attached modified query


<cfquery name="rsProductsSearch" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
SELECT DISTINCT p.product_ID, p.product_Sort, p.product_Name, p.product_FROM MerchantProductID
	LEFT JOIN tbl_prdtcat_rel pc
	ON p.product_ID = pc.prdt_cat_rel_Product_ID 
WHERE 
	p.product_OnWeb = 1
	AND p.product_Archive = 0
	<cfif application.allowbackorders EQ 0>
		AND s.SKU_Stock > 0
	</cfif>
		AND pc.prdt_cat_rel_Cat_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.c#">
	AND s.SKU_ShowWeb = 1
	ORDER BY
		p.product_Sort, 
		p.product_Name
</cfquery>

Open in new window

sorry there was typo table name missing with the query.

please remove if you have different table name rather than "Product p"
<cfquery name="rsProductsSearch" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
SELECT DISTINCT p.product_ID, p.product_Sort, p.product_Name, p.product_MerchantProductID FROM Product p
	LEFT JOIN tbl_prdtcat_rel pc
	ON p.product_ID = pc.prdt_cat_rel_Product_ID 
WHERE 
	p.product_OnWeb = 1
	AND p.product_Archive = 0
	<cfif application.allowbackorders EQ 0>
		AND s.SKU_Stock > 0
	</cfif>
		AND pc.prdt_cat_rel_Cat_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.c#">
	AND s.SKU_ShowWeb = 1
	ORDER BY
		p.product_Sort, 
		p.product_Name
</cfquery>

Open in new window

Kalmax: You have same answer as mine.
@armchang:

sorry, it could be hypothetical but my answer includes the modified code and table aliase, which was missing.
But, you should give me credit!
@14_east: P

lease consider the comment of armchang's with priority.