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!

Do you just want the first non null value of the 3? or are you looking to display 2 columns if 2 values are not null? — crthompson
– crthompson, Commented Jun 28, 2014 at 23:09
I want to receive all non null columns else dont return them :) So yes if 2 columns are not null display those 2. — Anwar
– Anwar, Commented Jun 28, 2014 at 23:10
you need a prepare statement to select the columns dynamically — Fabricator
– Fabricator, Commented Jun 28, 2014 at 23:12
And how do i do that? Sorry i don't have very much experience with sql can you give me a example :p — Anwar
– Anwar, Commented Jun 28, 2014 at 23:14
Your question doesn't make sense without further details. Suppose you have two rows: (null, value, null), (value, null,null). What do you want the output to look like? — Jim Garrison
– Jim Garrison, Commented Jun 28, 2014 at 23:29

wvdz · Accepted Answer · 2014-06-29 00:50:28Z

11

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.

edited Jun 29, 2014 at 0:50

answered Jun 29, 2014 at 0:45

wvdz

16.2k4 gold badges60 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related