Concat a string with a field value in a Join condition


Trying to select some values from an Opencart database. I can’t seem to think of the right syntax for this query.
There is a table called oc_url_alias which is a look-up for URL rewriting, it looks like:-

url_alias_id | query      | keyword
12345        |product=123 | url-text

I’m trying to get the values of the keyword column, I don’t see any reference to the url_alias_id in any other table, so have to use the product ID number which follows in the string product= (123 in the example data).

This is what i have for the query so far:-

			$fields = 'oc_product.product_id AS ocid,
				oc_product.sku AS id, AS title,
				meta_description AS description,
				oc_product.image' ;
			$query = "SELECT $fields FROM oc_product
				LEFT JOIN oc_product_description ON oc_product.product_id = oc_product_description.product_id
				LEFT JOIN oc_url_alias ON oc_url_alias.query = CONCAT('product_id=', `oc_product.product_id`)
				WHERE oc_product.status = '1'" ;

This gives an error that oc_product.product_id is an unknown column. It works if I replace oc_product.product_id in the CONCAT with an actual number, so it can’t be too far off.

So how do I correctly append the product ID number to the string in the comparison of the join?


remove the backticks

correct syntax would be


but you don’t need no steenkin backticks


That worked, thank you.

But I’m sure I tried it yesterday without backticks during the process of “trying different random things”. :thinking:
Maybe not, or I had something else wrong at the time.