intechfs
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
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
Does that mean if a column in row is null => should return that row or not ?
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.
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot for the help