SQL query, only select if column is not null else don't select

Question

This is what i am trying to do

 SELECT `a`, `b`, `c` FROM `tbl` WHERE `a` IS NOT NULL OR `b` IS NOT NULL OR `c` IS NOT NULL WHERE id = ?

If a and c are null and b isn't I still receive this result set\

 a      b     c 
____   ____   ____   

NULL  value   NULL

But i only want that it returns this

   b
  ____

  value

Thanks in advance!

Answer 1

If you want to get a row with two columns when there are two non-null columns, and 1 if there's only one, you have to dynamically create your query.

If you want to always have 1 column where each row contains a non-null value, you can do it with a union.

SELECT a FROM tbl WHERE a IS NOT NULL AND id = ?
UNION
SELECT b FROM tbl WHERE b IS NOT NULL AND id = ?
UNION
SELECT c FROM tbl WHERE c IS NOT NULL AND id = ?

If you want to able to know which from which columns the values come, you can do something like this:

SELECT 'col a' AS ColName, a FROM tbl WHERE a IS NOT NULL AND id = ?
UNION
SELECT 'col b', b FROM tbl WHERE b IS NOT NULL AND id = ?
UNION
SELECT 'col c', c FROM tbl WHERE c IS NOT NULL AND id = ?

Note: union also removes duplicate results. If you want to keep duplicates, use UNION ALL.