SQL join on two table and replace and return columns-CodePudding

I am working on some SQL query with joins and I want to get the corresponding value from another table to which matches with original table columns values.

For example I have two different tables named as Product and Category and they are as follows:

Products:

| id | name    | category1                 | category2    | category3   |
| -- | ------- | ------------------------- |--------------------------- |
| 1  | T-shirt | Cloths&Accessories~:/ID1  | Cloths~:/ID2 | Shirts~:/ID3|
|----|---------|---------------------------|------------- |-------------|
| 2  | T-shirt | Cloths&Accessories~:/ID1  | Cloths~:/ID2 | Shirts~:/ID3|
|----|---------|---------------------------|------------- |-------------|
| 3  | T-shirt | Cloths&Accessories~:/ID1  | Cloths~:/ID2 | Shirts~:/ID3|
|----|---------|---------------------------|------------- |-------------|
| 4  | T-shirt | Cloths&Accessories~:/ID1  | Cloths~:/ID2 | Shirts~:/ID3|
|----|---------|---------------------------|------------- |-------------|
| 5  | T-shirt | Cloths&Accessories~:/ID1  | Cloths~:/ID2 | Shirts~:/ID3|
|----|---------|---------------------------|------------- |-------------|

And Category:

| id | categories           |
| -- | -------------------- |
| 1  | Cloths & Accessories |
|----|--------------------- |
| 2  | Cloths               |
|----|--------------------- |
| 3  | Shirts               |
|----|--------------------- |

Problem is, in table Product there is this Cloths&Accessories~:/ID1 kind of Invalid strings stored under column names category1, category2 and category3 and I want to replace them with valid Category names from table Category. If you look at table Category the id of each category matches with string Cloths&Accessories~:/ID1 having ID1 in it.

For example: Cloths&Accessories~:/ID1 in this string ID1 is related to id=1 of table Category which is Cloths & Accessories

I have to replace and return all of category1, category2 and category3 columns of table Product with valid categories.

What would be the optimal SQL join query for this?

CodePudding user response：

You could use substring_index to extract the ID, and then join on it:

SELECT p.id AS id,
       p.name AS name,
       c1.categories AS category1,
       c2.categories AS category2,
       c3.categories AS category3
FROM   products p
JOIN   category c1 ON SUBSTRING_INDEX(p.category1, 'ID', -1) = c1.id
JOIN   category c2 ON SUBSTRING_INDEX(p.category2, 'ID', -1) = c2.id
JOIN   category c3 ON SUBSTRING_INDEX(p.category3, 'ID', -1) = c3.id