Select Query with no null in the report

Question

Table Parts

PartCode, PartName, Code1,Code2,Code3,Code4,Code5,Code6

00120,Test,15,null,24,35,12,null

I want in my report

00120,Test,15,24,35,12 

only no null

Answer 1

You should use IS NOT NULL. (The comparison operators = and <> both give UNKNOWN with NULL on either side of the expression.)

SELECT * 
FROM table 
WHERE YourColumn IS NOT NULL;

Just for completeness I'll mention that in MySQL you can also negate the null safe equality operator but this is not standard SQL.

SELECT *
FROM table 
WHERE NOT (YourColumn <=> NULL);

Edited to reflect comments. It sounds like your table may not be in first normal form in which case changing the structure may make your task easier. A couple of other ways of doing it though...

SELECT val1 AS val
FROM  your_table
WHERE val1 IS NOT NULL
UNION ALL
SELECT val2 
FROM  your_table
WHERE val2 IS NOT NULL

/And so on for all your columns/ The disadvantage of the above is that it scans the table multiple times once for each column. That may possibly be avoided by the below but I haven't tested this in MySQL.

SELECT CASE idx
         WHEN 1 THEN val1
         WHEN 2 THEN val2
       END AS val
FROM   your_table
        /*CROSS JOIN*/
       JOIN (SELECT 1 AS idx
                   UNION ALL
                   SELECT 2) t
HAVING val IS NOT NULL 

/Can reference alias in Having in MySQL/

Check full post here :

https://stackoverflow.com/a/5285461/5837967

A query can't exlude a declared column in a SELECT

How can you get the "Code2" result if you dont show the whole column ?