I have this table.

 ---- ----------- -------- ---------- 
| sku|  strength |  color | option_1 |
 ---- ----------- -------- ---------- 
|  1 | 40 lb     | (NULL) | strength |
|  2 | 50 lb     | black  | color    |
|  3 | (NULL)    | black  | color    | 
|  4 | (NULL)    | red    | color    |
 ---- ----------- -------- ---------- 

I want this table:

 ---- ----------- -------- ---------- ---------------- 
|sku | strength  | color  | option_1 | option_value_1 |
 ---- ----------- -------- ---------- ---------------- 
|  1 | 40 lb     | (NULL) | strength | 40 lb          |
|  2 | 50 lb     | black  | color    | black          |
|  3 | (NULL)    | black  | color    | black          |
|  4 | (NULL)    | red    | color    | red            |
 ---- ----------- -------- ---------- ---------------- 

I'm new to mysql. I'm trying to do this index-match or vlookup like query where option_1 determines which column to retrieve data from and send to option_value_1. I wasn't sure if I should do an inner join or a case clause since I have a much larger table with a lot more options than just color and strength.

CodePudding user response：

You can use a simple searched case expression, such as

case option_1
  when 'strength' then strength
  when 'color' then color
  when '...' then ...
else 'default option'
end as option_value_1
...

CodePudding user response：

I don't think you need a JOIN here. However, since you said there a lot more columns then it's going to be a long CASE expression to write. Also, if there's new column added then you need to remember to update the query from time to time. One way you can prevent that is using Prepare statement :

1. Setting variables as NULL just in case

SET @sql := NULL;
SET @columns := NULL;

2. Constructing the CASE expression and assign to @columns variable:

SELECT GROUP_CONCAT(DISTINCT
        CONCAT('WHEN "',option_1,'" THEN ',option_1) SEPARATOR ' ') 
         INTO @columns
FROM mytable;

3. Constructing the final query by appending the previously constructed @columns variable into the @sql variable:

SELECT CONCAT('SELECT *, CASE option_1 ',@columns,' END AS option_value_1 FROM mytable;') 
        INTO @sql;

4. Prepare, execute and deallocate the final generated query in @sql:

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

To check the variables you can run:

SELECT @columns;
SELECT @sql;

This will be good for you to test the query especially when the execution returned error; so that you can pin-point where the problem is and fix.

Note:
It's not necessary to have two variables in constructing the final query. I mean, you can just do a straight query construction to @sql by combining the @columns generating query with the @sql generating query. I personally separate them to make it easier to understand the components.

Demo fiddle