Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to check all the column values of a corresponding value are null or not null

So I have a table where I have column A, B, C,D. Column A has values which have corresponding multiple values in column B.I need to check if all the values in column C and D are null for the column B values.

Column A Column B Column C Column D
100 5 0 3
100 6 1 5
100 7 null null

If you observe here. Column A has same value. Consider that a single shop. Consider Column B to be the products of a shop. Column C can be the price and Column D may be the discounts.

In the above table if I want to find out the shop which has no cost set for any product and no corresponding discount. then if I use Is Null for column C and Column D it will give me 3rd row. But I have other products from Shop 100 having cost and discounts set.

My expectations:

I want to fetch the shops for which all the cost and discount are null for all the products.

  1. where all the column values are null for Column C and D,

  2. all the column values are not null for Column C and D,

Tags:

2

Answers


  1. 0

    you mean something like this (assuming it’s SQL)

    Select * from table
Where A IS NULL and B IS NULL and C IS NULL and D IS NULL;

    And

    Select * from table
Where A IS NOT NULL and B IS NOT NULL and C IS NOT NULL and D IS NOT NULL;
    Login or Signup to reply.
  2. 0

    I would suggest using concat and concat_ws in MySQL.

    If any of the values are null concat returns null.

    In your case would be.

    select colA,
       colB,
       colC,
       colD
from your_table
where concat(colA,colB,colC,colD) is not null 
or concat_ws(colA,colB,colC,colD) is null

    See example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search