Results 1 to 15 of 15
  1. #1
    Join Date
    May 2009
    Posts
    10

    Unanswered: returning columns that don't have null/blank values?

    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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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 ...>
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vapor
    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.
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2009
    Posts
    10
    Quote Originally Posted by healdem
    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 ...>
    Could it be a little more "generic"? Because in many tables I have about fifty columns each.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vapor
    Could it be a little more "generic"?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2009
    Posts
    10
    Quote Originally Posted by r937
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vapor
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2009
    Posts
    10
    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vapor
    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:
    Code:
    SELECT * FROM cities LIMIT 1
    then use Ruby to inspect each column, and don't display it if it contains a blank or NULL

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2009
    Posts
    10
    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.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vapor
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    ...by not wanting to bother with specifying the 50 columns as IS NOT NULL in your query.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •