SELECT only IS NOT NULL columns with LEFT JOIN



  • 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.

    https://shopee.com.my/%F0%9F%8C%B8READY-STOCK%F0%9F%8C%B8Korean-Striped-shirt-Woman%27s-Fashion-Casual-Loose-Tops-Long-sleeve-T-shirt-clothes-i.322596772.4157167158?sp_atk=c77b154e-7cf7-4c5a-a16b-e2ce3febe9dc&xptdk=c77b154e-7cf7-4c5a-a16b-e2ce3febe9dc



  • 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;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2