0

I have one table like this:

urlalias

And one like this:

status

What I want to do is select that "1" from status in the second table if the product_id number matches the number in the query column in the first table and is prefixed by product_id=. category_id and others should be ignored. Is something like this even possible?

1
  • 2
    Hi there- welcome to the site! The join is possible, but the specific implementation will depend on which DBMS you are using. Can you please add a tag indicating your platform? (SQL Server, Oracle, Sqlite, mysql etc) Commented Oct 8, 2015 at 15:25

2 Answers 2

1

I used Magier's example to come up with this:

SELECT oc_url_alias.keyword, oc_product.status, oc_product.date_modified,
REPLACE(query, 'product_id=', '') AS keymatch
FROM oc_url_alias
JOIN oc_product on REPLACE(query, 'product_id=', '') = oc_product.product_id
WHERE oc_product.status = 1;

which outputs exactly what I need.

Thanks for all the help everyone posted.

0

Your data does not really look very normalized.

Besides that you could try something like that:

select *, REPLACE(query, 'category_id=', '') as s From table1 
join table2 on REPLACE(query, 'category_id=', '') = table2.product_id 
1
  • I used your example to come up with this: SELECT oc_url_alias.keyword, oc_product.status, oc_product.date_modified, REPLACE(query, 'product_id=', '') AS keymatch FROM oc_url_alias JOIN oc_product on REPLACE(query, 'product_id=', '') = oc_product.product_id WHERE oc_product.status = 1; and it outputs exactly what I need. Thank you!
    – Kanyin
    Commented Oct 8, 2015 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.