SQLite - sql question 101

I would do something like this:

  1. select * from cars_table where body not equal to null.
  2. select * from cars_table where values not equal to null And id = "3"

I know the syntax for 'not equal' is <>, but I get an empty results.

For the second part, I want to get a result set where it only returns the columns that have a value. So, if the value is null, then don't include that column.

Thanks

Answers


You cannot use equality operators for nulls, you must use is null and is not null.

So your first query would be:

select * from cars_table where body is not null;

There's no easy way to do your second operation (excluding columns that are null). I'm assuming you mean don't display the column if all rows have NULL for that column, since it would be even harder to do this on a row-by-row basis. A select statement expects a list of columns to show, and will faithfully show them whether they are null or not.

The only possibility that springs to mind is a series (one per column) of selects with grouping to determine if the column only has nulls, then dynamically construct a new query with only columns that didn't meet that criteria.

But that's incredibly messy and not at all suited to SQL - perhaps if you could tell us the rationale behind the request, there may be another solution.


Comparing to NULL is not done with <>, but with is null and is not null :

select * 
from cars_table
where body is not null

And :

select *
from cars_table
where values is not null
    and id = '3'

NULL is not a normal value, and has to be dealt with differently than standard values.


In SQL, NULL is an unknown value. It is neither equal-to nor not-equal-to any other value. All comparison operators (=, <>, <, >, etc.) return false if either of the values being compared is NULL. (I don't know how old you are, so I can't say that you're 24, but I also can't say that you're not 24.)

As already mentioned, you have to use IS NULL or IS NOT NULL instead when testing for NULL values.


Thank you all for your answers.

Well, I have a table with a bunch of columns. And I am going to search a particular car, and get the values for that car...

car | manual | automatic | sedan | power brakes | jet engine

honda | true | false | false | true | false

mazda | false | true | false | true | true

So, I want to do -> select * from car_table where car='mazda' and values not equal to false

So then I just iterate over the cursor result and fill up the table with the appropriate columns and values. Where values are columns. I guess I replace values with * for columns

I know I could do it programmatically, but was thinking I could do this just by sql


Need Your Help

The Wordpress Loop and adding images to the HTML5 Canvas Element

wordpress html5 loops canvas while-loop

So I'm trying to create canvas elements using the information passed back by "The Loop". The following is my code. It works, but only kind of, and only on the last canvas created by the loop.

Javascript click function not working

javascript javascript-events

I need to null the value in text box on click, currently I have written a code as such