CODEWITHSUNDEEP
mysqljoin
emma
emma

Reputation: 759

Joining tables with different value prefix

I'm trying to join two tables but the problem is that in the second table the value that is the same as in table one has a prefix to it(this tables are generated after opencart instalation - demo data):

Table 1: category
 -----------------------------
| category_id | category_name |
|-----------------------------|
|     1       |  Components   |
|     2       |    Laptops    |


Table 2: seo_url
 ------------------------------------------
| seo_url_id  |    query      |   keyword  |
|------------------------------------------|
|     35      | category_id=1 | components |
|     78      | category_id=2 | laptops    |

So the id of a category is in column category_id in Table 1 and it is a number but in Table 2 it is in column query and it has a prefix of category_id= and then the id x(in case of category laptops x being 2).

Can somebody please help me understand how i could join this tables in this situation?

So far i was trying to add category_id= + like this:

SELECT a.id, a.category_name, b.query 
FROM category AS a
INNER JOIN seo_url AS b
ON a.category_id = 'category_id=' + b.query

P.S I tried ON 'category_id=' + a.category_id

P.S.S There are also product_id so i don't know if i could use LIKE but i was thinking about it, searched for it and couldn't find a way to make it work.

Thank you! D:

Upvotes: 0

Views: 669

Answers (2)

Daniel Duarte
Daniel Duarte

Reputation: 494

In MySQL, use the function CONCAT(...) that can append strings and numbers, and it is compatible with different versions of that database.

Your fixed query would be:

SELECT a.id, a.category_name, b.query 
FROM category AS a
INNER JOIN seo_url AS b
ON CONCAT('category_id=', a.category_id) = b.query;

Your table seo_url already has the 'category_id=' in the values of the field query, so you don't need to append it.

Additionally, I'd recommend you to name the table aliases with more representative names, instead of using a and b.

Hope this helps you to solve your problem!

Upvotes: 2

Raymond Nijland
Raymond Nijland

Reputation: 11602

Use nested REVERSE functions with + 0 to autocast "parse" out the integer.

Query

SELECT
 REVERSE(REVERSE('category_id=2') + 0)
UNION ALL 
SELECT 
 REVERSE(REVERSE('category_id=21') + 0)

Result

| REVERSE(REVERSE('category_id=2') + 0) |
|---------------------------------------|
|                                     2 |
|                                    21 |

see demo http://sqlfiddle.com/#!9/340e01/530

Use it in your query.

Query

SELECT a.category_id, a.category_name, b.query 
FROM category AS a
INNER JOIN seo_url AS b
ON a.category_id = REVERSE(REVERSE(b.query) + 0)

Result

| category_id | category_name |         query |
|-------------|---------------|---------------|
|           1 |    Components | category_id=1 |
|           2 |       Laptops | category_id=2 |

see demo http://sqlfiddle.com/#!9/ef5781/1

Upvotes: 1

Related Questions