SQL (mysql) - If a given row on a given column as a certain value, don't list that column.

Question

I have a query that retrieves some data, among those data I have some that are returned with a value like 0. I would like the query to NOT return the columns when that's the case.

How can we do such a thing?

Regards, MEM

Answer 1

select <column_name> from <table_name> where <column_name> <> 0.0

Here is all the data in a sample database. Notice how there are 3 rows with one having a zero value for the num column.

mysql> select * from test_tbl;

     +------+----------+
     | num  | some_str |
     +------+----------+
     |    0 | matt     |
     |    2 | todd     |
     |    3 | Paul     |
     +------+----------+
     3 rows in set (0.00 sec)

Now lets use the where clause to specify the rows we want to ignore (it's a little bit of reverse logic because we are actually specifying what rows we want).

mysql> select * from test_tbl where num <> 0.0;
     +------+----------+
     | num  | some_str |
     +------+----------+
     |    2 | todd     |
     |    3 | Paul     |
     +------+----------+
     2 rows in set (0.00 sec)

Note: This will only work without getting messy if 0 is the only value you are worried about. A better way would be to allow nulls in your column and then you can check to see if they are non-null in the where clause.