We help IT Professionals succeed at work.

SQL syntax help/error

14_east
14_east asked
on
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

Comment
Watch Question

Senior Developer
CERTIFIED EXPERT
Commented:
After your FROM clause, there should supposedly a table name supplied instead you have put a LEFT JOIN without the left table link to the right table.

like e.g.
SELECT * FROM TableA LEFT JOIN TableB ON TableA.product_ID = TableB.prdt_cat_rel_Product_ID
CERTIFIED EXPERT

Commented:
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

CERTIFIED EXPERT

Commented:
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

Armand GSenior Developer
CERTIFIED EXPERT

Commented:
Kalmax: You have same answer as mine.
CERTIFIED EXPERT

Commented:
@armchang:

sorry, it could be hypothetical but my answer includes the modified code and table aliase, which was missing.
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
But, you should give me credit!
CERTIFIED EXPERT

Commented:
@14_east: P

lease consider the comment of armchang's with priority.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.