1

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

7
  • 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.
    – Akina
    May 19 at 4:38
  • 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.)
    – Rick James
    May 22 at 4:10
  • 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
    May 22 at 12:09
  • Thanks @RickJames Please read above comment.
    – AmQ7
    May 22 at 12:10
  • @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?
    – Rick James
    May 22 at 15:54

1 Answer 1

0

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;
1
  • 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
    – AmQ7
    May 23 at 21:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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