NULL field


Is there a way to run a query and check for a NULL field
I have a DB with a NULL field right now. If it happens to be NULL, the queries will completely skip it. For example, if I have a table with a name field and it is set to NULL and I run

Code: SELECT name FROM table WHERE name != 'bob'

Any and all NULL fields are skipped as if they dont exist. I even tried

Code: SELECT name FROM table WHERE name = NULL SELECT name FROM table WHERE name = 'NULL'

And I get nothing. What is the proper way, if any
Posted On: Monday 29th of October 2012 05:44:47 AM Total Views:  437
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




value if it exists, null if it don't (was "Very Basic Question")

Forum, I suppose this could be an indication that I have designed the database wrong, but here is the question .. I have a SELECT AND A JOIN that can produce the answer I am looking for from two fields depending on the WHERE statement varables. I can get can get three different options: Option 1 +-----------------+------------------+ | Island.Island_ID | Domain.Island_id | +-----------------+------------------+ | NULL | 73 | +-----------------+------------------+ Option 2 +-----------------+------------------+ | Island.Island_ID | Domain.Island_id | +-----------------+------------------+ | 73 | NULL | +-----------------+------------------+ Option 3 +-----------------+------------------+ | Island.Island_ID | Domain.Island_id | +-----------------+------------------+ | NULL | NULL | +-----------------+------------------+ The ideal result I want it just 73 if it exists in any of the fields and NULL if it dont.
VIEWS ON THIS POST

127

Posted on:

Friday 12th October 2012
View Replies!

Select and set value if null

I cnat figure it out can someone pls help me out: select `name` from table for example and if `name` is null make the result appear as 'unknown' for example
VIEWS ON THIS POST

170

Posted on:

Friday 12th October 2012
View Replies!

Rows returning null when 'case' should prevent

My query is Code: SELECT CONCAT( CAST( d.deptID AS CHAR),' - ',d.deptName) AS department, case(a.aliasOf) when null then 1 else a.aliasOf end FROM departments d LEFT JOIN aliases a ON a.deptID = d.deptID; But ultimately when a.aliasOf is null in this case, it's the value of a.aliasOf, but also many instances of null, never a '1' What am I doing wrong
VIEWS ON THIS POST

200

Posted on:

Friday 12th October 2012
View Replies!

Inserting null value

I have a table with the schema given below. SQL> desc master_object; Name Null Type ------------------------------- -------- ---- MSISDN NOT NULL VARCHAR2(60) PLATFORMID NOT NULL NUMBER(10) NUMBERTYPE NOT NULL CHAR(1) I want to insert a value into this table, with NUMBERTYPE as null, but cannot do the same. The ascii value in this column must be 0. I get the below outputs, while running select query SQL> select ascii(numbertype) from master_object where msisdn='919417234876'; ASCII(NUMBERTYPE) ----------------- 0 SQL> select numbertype from master_object where msisdn='919417234876'; N - Is there any way to insert or update the value to null into this column
VIEWS ON THIS POST

180

Posted on:

Friday 12th October 2012
View Replies!

Optional Parameters, allowing nulls

I'm trying to write a stored proc where the user can query my database for matching records. There are several columns that the user could query on, eg ColA, ColB, ColC. In order to achieve this, I currently have: Code: CREATE PROCEDURE DO_MY_SEARCH ( @ParA = null, @ParB = null, @ParC = null ) AS SELECT * FROM my_table WHERE (@ParA is NULL OR ColA = @ParA) AND (@ParB is NULL OR ColB = @ParB) AND (@ParC is NULL OR ColC = @ParC) GO This seems to work fine, and the user can specify as many of ParA, ParB or ParC as they like. However, how can I adapt this (or start again) to allow the user to be able to search for fields that are currently null. For example, return all records where ColC is null and ColA = 'Test' Any ideas
VIEWS ON THIS POST

143

Posted on:

Friday 12th October 2012
View Replies!

column can not be null

Code: 'f` INT(10) UNSIGNED NULL DEFAULT NULL Code: update t set f = null where f = 0 --> err 1048 column f can not be null
VIEWS ON THIS POST

298

Posted on:

Tuesday 16th October 2012
View Replies!

sub table versus null values

I've created two tables and a one-to-one relationship between them: stands and standsestimated. I created standsestimated with three columns NOT included in stands. (These are standsestimated.stocking, standsestimated.harvestsystem, and standsestimated.operator). I knew that half my total stands (primary key being stands.standID) would have NULL values for these columns. So, I decided to have a sub-table for the stands that would have values for these columns. My question is this: from an efficiency point of view, am I better with the two tables, neither with null values, at least concerning these three columns, or am I better with only one table (stands), including the tree columns, with roughly half the rows being null value
VIEWS ON THIS POST

176

Posted on:

Sunday 21st October 2012
View Replies!

insert of aggregated value becomes null?

When i do call this in console prompt I get the correct value, but when it comes to inserting it, it inserts a null value What do you think of that Code: INSERT INTO clist2 SELECT CEIL((o + m + p)/6)* Cost) as FinalCost ...
VIEWS ON THIS POST

240

Posted on:

Sunday 21st October 2012
View Replies!

Column 'PostDate' cannot be null

I've created a small app for me and some friends to keep track of our projects. Everything worked out ok while running the app at home but when we uploaded it to the server it didn't work I mean, we can browse it but the problem is when we try to insert something into db this error is displayed: Column 'PostDate' cannot be null which is the mysql error thrown here: Code PHP: $queryInsert = @mysql_query("INSERT INTO messages ( PostDate, MsgContent, MsgTitle, AuthorName) VALUES ( convert_tz(CURRENT_TIMESTAMP(), 'SYSTEM', 'GMT') , '".$msgContent."' ,'".$msgTitle."' ,'".$author."')") or exit(mysql_error()); when we try to "send" each other a "message" (not a message like on YIM). the problem is with the Code MySQL: convert_tz(CURRENT_TIMESTAMP(), 'SYSTEM', 'GMT') function which returns null (when php is runing as a FastCGI application \t ). I've searched the web to find an answer to this problem but couldn't find anything to help me out... our web hoster can support the following types of configuration for php: CGI applicationFastCGI applicationISAPI extension for php version 5.2.5 on a ms windows environment. I tried all those three options, also I tried to set up an ini file but it didn't work .. why does it have to be that complicated\t I appreciate any help on this one as it's driving me crazy...
VIEWS ON THIS POST

272

Posted on:

Sunday 21st October 2012
View Replies!

Inserting time as null

Hi all, I've really really really confused and really need your help please! I'm having trouble trying to insert data from a form into a mysql db, namely time. If a user only enters data for one day and leaves the other days of the week blank then it automatically defaults to 00:00:00. How do I tell it to default the insert to nothing, so that when I fetch the data and display it in a php echo it will only show the days with info. I hope I've explained that properly Maybe I'm going about it the wrong way You're help is very much appreciated Gary
VIEWS ON THIS POST

185

Posted on:

Monday 22nd October 2012
View Replies!

AVG returning 0, not null

I'm writing a script to allow visitors to rate articles on my site. Sometimes a question does not apply to that particular article or a person is just too lazy to fill out the entire form. Either way, I'm passing NULL to the MySQL database if the question was not filled out. I want to do averages for each article for each question I ask. The problem is that I'm getting 0 only when I use AVG () even though the MySQL site says that should return a null if empty. It is not. I have a "control group" table pulling the same data without using AVG. Of course, every vote is visible, but it's clear that null is working properly in that example. Here's is a shortened version of my code: PHP Code: $queryA="SELECTURL,AVG(Vote),AVG(A1),AVG(A2),AVG(A3)\tFROMGalleries\tGROUPBYURL\t"; $result=mysql_query($queryA)ordie(mysql_error()); while($row=mysql_fetch_array($result)){ \t>       \t\t\t\t
VIEWS ON THIS POST

184

Posted on:

Monday 22nd October 2012
View Replies!

Mysql import inserting only null values into table

, I took dump of a database 'new' with only one table 'backup' using the following command : mysqldump -u root -p -h localhost new --result-file=C:\Users\Administrator\Desktop\backup.sql When trying to restore using : mysqlimport -u root -p new C:\Users\Administrator\Desktop\backup.sql, it gave me an error mysqlimport: Error: 1366, Incorrect integer value: '-- MySQL dump 10.13 Distrib 5.5.24, for Win64 (x86)' for column 'id' at row 1, when using table: backup. I read somewhere to disable strict mode so i edited my.ini and commented out sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION". Now when I try to import, all that goes into the database table are null values. None of the data in the .sql file is acutally making it into the database. Please help. tnx
VIEWS ON THIS POST

236

Posted on:

Wednesday 24th October 2012
View Replies!

SELECT nulls NOT zeros

, I have an INT field which accepts NULL values and can accept 0 values, how do i select records which have NULL values without selecting records with 0 values\t i tried: Code: SELECT * from tbl WHERE field != 0 it doesn't return any records with zeros OR nulls, i want it to return records with NULLs.. Help is great appreciated,
VIEWS ON THIS POST

172

Posted on:

Wednesday 24th October 2012
View Replies!

Null to not null

, When modifying a NULL column to NOT NULL, I am getting the error "ERROR 1265(01000): Data truncated for column ...." on mysql 5.0.27-community-nt (windows xp). When i try the same thing on 5.0.45-community(Redhat), I do not get any error. Here's the schema and some sample data. Code: create table check_null( id int auto_increment not null primary key, nname varchar(32) not null, phone varchar(16) )engine=innodb charset=utf8; insert into check_null (id,nname) values (null, 'Alpha'), (null, 'Beta'), (null, 'Gamma') ; Here's the sql statement for adding a not null constraint to a column. sql Code: Original - sql Code alter table check_null modify column phone varchar(16) not null;
VIEWS ON THIS POST

185

Posted on:

Wednesday 24th October 2012
View Replies!

Set field to default null drop foreign key

in a table I've a field like `payment_id` CHAR(5) NOT NULL, with fk CONSTRAINT `order_fk_payment` FOREIGN KEY (`payment_id`) REFERENCES `payment_method` (`payment_id`) ON DELETE CASCADE ON UPDATE CASCADE now I need to do on client request (there are customer with not payment_id) ALTER TABLE `order` MODIFY `payment_id` CHAR(5) default NULL, so I'm wondering if I should get rid of the fk or not like ALTER TABLE `order` DROP FOREIGN KEY `order_fk_payment`;
VIEWS ON THIS POST

436

Posted on:

Thursday 25th October 2012
View Replies!

is "%" + Null still null?

if i do: Code: mycolumn LIKE ISNULL('%'+@Param+'%', mycolumn) when @Param is set to null is '%'+@Param+'%' still null and therefore the mycolumn is set to equal itself Or does the second part never run because the first always has a value cheers , Originally Posted by r937 steve, that's the same as the ISNULL/COALESCE function Not exactly - ISNULL / COALESCE uses the condition as the value I used @Param as the condition and '%'+#Param+'%' as the value. Look again: Code: LIKE (CASE WHEN @Param IS NULL THEN mycolumn ELSE '%'+@Param+'%' END) Minus the comma, it will work, even if '%'+null != null , Perhaps the simplest solution would be: Code sql: WHERE (@Param IS NULL OR mycolumn LIKE '%'+@Param+'%') If @param is null, it should never get to the like comparison.
VIEWS ON THIS POST

142

Posted on:

Thursday 25th October 2012
View Replies!

How to check if field is empty or null?

I have a problem. I store some avatar images in a database. And these avatars are optional for each user. A general unknown avatar image is shown if the avatar field for the specified user is empty or null. Showing the avatar is a 2 step process. First I want to check if the avatar field is not null. If it is not then retrieve the data and show it on the web page. How to check if a field is null or not without retrieving the whole field's data from the database
VIEWS ON THIS POST

152

Posted on:

Thursday 25th October 2012
View Replies!

null fields excluded in search

I'm having a problem with my search results page. I'm pulling my dropdown menu from a table. In my column "agent" some rows are null. When I am doing a search, no rows will show up where "agent" is null. Should I have something in the "value" that would include the null fields Any ideas
VIEWS ON THIS POST

200

Posted on:

Thursday 25th October 2012
View Replies!

where this != 'that' and null entries

I see what is happening but dont have a clue why. PHP Code: $sql=@mysql_query("SELECT*FROMpagesWherecategory='$linkcat'ANDextra!='hidden'ORDERBYpos"); I would expect this to ignore "null" entries in the "extra" column and just NOT match any entry that is named specifically "hidden". as it is, it works like, don't match "hidden" and dont match "null" \t it grabs everyting until this part is added AND extra !='hidden' then it ignores hidden and null entries. ok, overexplained but can someone explain why it acts this way I am using mysql and this is an sql query. am I posting in the right forum
VIEWS ON THIS POST

174

Posted on:

Thursday 25th October 2012
View Replies!

Select all null??

two Db's 1. clubs - clubId | clubName|..... 2. recentClubsList - id | clubId | dispOrder all clubs are listed in clubs. only a few are represented in recentClubsList. I want to query a result set that includes only clubs that do not show up in the recentClubsList Db. I have tried this query Code: SELECT CONCAT(clubName, ', ', state ) AS theName, c.clubId, rc.id, rc.clubId FROM clubs c LEFT JOIN recentClientsList rc ON (rc.clubId=c.clubId) ORDER BY c.clubName It is very close as it returns all clubs but with null values when there is no rows within recentClubsList. Queries confuse me so i may not even be close to right here. any ideas Ras , i got it working, though i went a different route. I added a column to the clubs Db that i can query over. Ras
VIEWS ON THIS POST

162

Posted on:

Thursday 25th October 2012
View Replies!