Hi,

I am new to DB and queries. I want to return a single row but only those columns (and data) of row that are not blank or do not have NULL value.
I don't know how to do this.

Please help.
Thanks.

select my, comma, separated, column, list from mytable
where
my IS NOT NULL and
comma IS NOT NULL and
separated IS NOT NULL and
column IS NOT NULL and
<.... insert any other criteria you require ...>

in the SELECT clause, list the columns that you want

when you receive these columns in the result set, there may be an occasional blank or NULL amongst the columns on any given row

note that the columns which have blank or NULL might vary from row to row!!

here's what you do when you encounter a blank or NULL: ignore it

Could it be a little more "generic"? Because in many tables I have about fifty columns each.

not within SQL No, at least not as far as I know (which truth be told isn't all that much)

I suppose you may be able to use the coalesce statement and test if that is null

if you want a generic approach then consider doing it in your front end

yes, my suggestion is as generic as it gets --

return the rows, and skip over (i.e. ignore) any column values that are blank or NULL

Can you please help me with the query?
Thanks

sure, i'd be glad to

what is the name of the table?

which columns did you want to return?

and how do you determine which row you want?

finally, which programming language are you using to call the database from?

table is cities
I want to return all columns (regardless of names) that do not have null value.
I want to return first row.
and I am using Ruby to run query.

okay, this is pretty easy

here's your query:then use Ruby to inspect each column, and don't display it if it contains a blank or NULL

lol
so, there's no easier query for it?
because someone told me that on performance aspect it would be better to not to include null columns at first place.

that someone is wrong, and does not know what he is talking about

they may have a point in terms of not sending data to the client that isn't needed, but you've ruled that out by not wanting to bother with specifying the 50 columns as IS NOT NULL in your query.

mind you how you could get to 50 columns in a table called 'cities' is beyond me

that's not how i understood the requirement, healdem

your suggestion, to write IS NOT NULL for every column, would mean that entire rows would be dropped from the result set if even one column has a blank or NULL

the way i understood the requirement, he wants to return a single row (which one? that's not yet clear), but return only the non-blank and non-NULL column values in it -- in other words, don't return blank and NULL column values amongst the columns being returned

which is clearly not feasible

yeah I guess it all comes down to interpetation

I read the question as I want to find the rows which don't contain blank columns