SELECT only IS NOT NULL columns with LEFT JOIN

Question

I have a query where i need to select product options from multi tables.

SELECT 
p.option_id, 
p.product_id, 
p.sku, 
p.qty, 
p.price,
c.color_label,
s.size_label,
m.material_label,
v.variation_label
FROM product_options p
LEFT JOIN option_color c ON c.color_id = p.color_id
LEFT JOIN option_size s ON s.size_id = p.size_id
LEFT JOIN option_material m ON m.material_id = p.material_id 
LEFT JOIN option_variation v ON v.variation_id = p.variation_id
WHERE p.product_id = 1 ;

This works fine. However; i get NULL values Like so

enter image description here

Business rule: as follows if any of the columns contains NULL means the whole product variation (column) of that particular option will be NULL.

MY QUESTION

How i can dynamically remove any of the columns once there result contain NULL.

Example from the above pic.

material_label = NULL

then the whole column should be removed.

I hope i explained it well.

EDIT

Just adding further explaanation.

What im trying to achive is as follow

1. Get specific product from the DB with its variants(try and minimize non required data).
2. List item then do some array cleaning and grouping then maybe convert it to JSON using PHP.
3. then using javascript to get user selection on the product.

Maybe this link can clear things

• Akina: Firstly - use INNER JOIN instead of LEFT (study the difference carefully !!!). Then, if some column in some options table may contain NULL, add according WHERE condition.
• Rick James: There are two ways to get NULL -- The JOIN may not find the matching row in the righthand table, or the matching row of the righthand table exists but contains NULL. Which case(s) are you concerned with? (I think the solution depends on your answer.)
• AmQ7: Thanks @Akina i think im over thinking it. My current solutions is the common way of handing such a case. i need to find a way through programming language im using to enhance my results. from the data you can tell that its products variants. once user select color then system should change selection based on the available sizes of that color. nevertheless. i think my solution need a combination of the levels, First get data from DB exactly as i did above then PHP to make data in a json Maybe and leave the rest for Javascript to handle
• AmQ7: Thanks @RickJames Please read above comment.
• Rick James: @AmQ7 - "will be NULL" -- I read that to mean that you want to change the column to say NULL in each row. Am I misunderstanding you? Please provide an example for each case, and show both input and output. Perhaps simplify the table to only 2 label columns? Or can you show all cases by using different columns?

Answer 1

it's better to use INNER JOIN instead LEFT JOIN, but if you don't want to change your query, you can add this at the end of your query:

WHERE p.product_id = 1 
AND m.material_label IS NOT NULL
AND v.variation_label IS NOT NULL;

• AmQ7: thanks @YousefRahimy . using your answer i will lose the whole row if it contains Null. I regrads to Inner Join. Could you provide me with a sample. thanks

Answer 2

You should change the SELECT clause altogether

For example, combine the output of m.material_label and v.variation_label

CONCAT(
 IFNULL(m.material_label,'')
 ,IF(IFNULL(m.material_label,'')='','',':')
 ,IFNULL(v.variation_label,'')
) material_variation

This will return an empty string if m.material_label and v.variation_label are NULL.

If you would like to combine all the columns so that material_variation is completely gone, you can try something like this:

SELECT
CONCAT(
 'option_id : ',p.option_id
,',product_id : ',p.product_id
,',sku : ',p.sku
,',qty : ',p.qty
,',price : ',p.price
,',color_label : ',c.color_label
,',size_label : ',s.size_label
,IF(IFNULL(m.material_label,'')='','',CONCAT(',material_label:',m.material_label))
,IF(IFNULL(v.variation_label,'')='','',CONCAT(',variation_label:',v.variation_label))
)
FROM product_options p
LEFT JOIN option_color c ON c.color_id = p.color_id
LEFT JOIN option_size s ON s.size_id = p].size_id
LEFT JOIN option_material m ON m.material_id = p.material_id 
LEFT JOIN option_variation v ON v.variation_id = p.variation_id
WHERE p.product_id = 1 ;

This will return the entire row as key/value pairs

• If m.material_label is NULL, it displays an empty string. Otherwise, the key/value pair.
• If v.variation_label is NULL, it displays an empty string. Otherwise, the key/value pair.