I want to display a column in a select query according to the following :
1- If result is not null, display the column.
2- If the result is null, do not display the column.
I tried this but it doesn't work, it displays all the time the column (null or not null)
if(surname is null,'',surname) as surname
If you still want an example... here it is.
B.T.W. this example has multiple columns (id and givenname). If you only have that one surname column you could do this a whole other way but it would depend on the rest of your "script" how you would do it. (because the select would not return ANY columns in that case and that's invalid for a select)
CREATE TABLE my_table( id int(11) NOT NULL AUTO_INCREMENT, surname VARCHAR(20), givenname VARCHAR(20), PRIMARY KEY (id) ); INSERT INTO my_table(givenname, surname) VALUES ("me", null), ("you", null), (null, null), ("and sombody else", null);
SET @nr_names = (SELECT COUNT(*) FROM my_table WHERE NOT(surname IS null)); SET @fields = (SELECT if(@nr_names=0, "id, givenname", "id, givenname, surname")); SET @query = CONCAT("select ", @fields, " from my_table"); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Will result in
1 me 2 you 3 (null) 4 and sombody else
Chaning the data to
INSERT INTO my_table(givenname, surname) VALUES ("me", null), ("you", "works"), (null, null), ("and sombody else", null);
1 me (null) 2 you works 3 (null) (null) 4 and sombody else (null)
Working SQL Fiddle.
(unfortunately SQL Fiddle isn't what it used to be with the lack of speed these days)
I can't get SQL Fiddle to work anymore.
Here is the alternative: RexTester
See there is no column surname. Change one null to text in the insert data and click "Run it".