I have two database tables that contain a product_model field. On one table, the product model starts with a prefix (TL_) and has no prefix in the other table. I want to use those fields to join a query and am having trouble figuring out how to get it working. This always returns with zero results, so it appears it is not working. See below:
SELECT a.id as app_id
FROM
applications as a
JOIN products as p
WHERE p.products_model=CONCAT('TL_',a.products_model)
AND p.products_id=29206
I would ultimately change the natural key to be the same between tables. Both for proper design standards and optimization purposes. Appending TL to the base model name seems like display logic to me and has no place being stored in the database.
query looks okay, i suspect the data isn’t what you think it is
but just to be sure, try it like this –
SELECT a.id as app_id
FROM applications as a
INNER
JOIN products as p
[COLOR="Blue"][B]ON [/B][/COLOR]p.products_model = CONCAT('TL_',a.products_model)
WHERE p.products_id = 29206