Link to home
Start Free TrialLog in
Avatar of intechfs
intechfsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How do I select all not null values in a MySQL query

I am trying to write a MySQL query which will find all values in a table that aren't null.

I won't know in advance what the names of the columns are so can't specifically name them when using NOT NULL.

What I want is something like this which would actually work:
SELECT * FROM table WHERE * IS NOT NULL
Avatar of Sreedhar Vengala
Sreedhar Vengala
Flag of Australia image

Does that mean if a column in row is null => should return that row or not ?
Avatar of theGhost_k8
you must use All column names instead of *.
If you do not have the column names in advance, it is more difficult and I think not possible just with SQL. A solution is to write a little function that gives you all rows (or just the fields) with Null-Values in one of the fields. There are two ways:
1) creates a new (dummy-)table and writes all rows to this table.
2) writes just the unique index (primary key) of your table to a dummy-table. It assumes that your table has a primary key. If not you can simply add a field autonumber, so all rows have a key. With a join between you table and the key-table you get all rows containing a null-value.

For 2) I can provide you a little script, if this is a way you might go.
Avatar of intechfs

ASKER

sree_ven I am wanting to have those not returned.

coffeeshop - could you please show me the script you would use for 2.I'm taking this out in Perl and will probably just loop through the results and ignore anything null but if what your suggesting looks more efficient I may do that

Thanks a lot for the replies.
ASKER CERTIFIED SOLUTION
Avatar of coffeeshop
coffeeshop
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot for the help