Generally when one has this problem one joins this query with something from
which one can get the whole list, something like this:
SELECT identifier, IFNULL(c, 0) AS Good,
(query with COUNT) AS P RIGHT JOIN table-of-identifiers ON P.identifier =
table-of-identifiers.identifier
The c is
2011/10/02 15:01 +0200, Jigal van Hemert
You are not using NULL as the original concept of it was. NULL means that the
value is undefined or unknown.
That is quite true, especially in a table. But, almost from the beginning, NULL
was overloaded:
set @m = (select sins from emailinglist where
Hi,
On 1-10-2011 21:51, Halász Sándor wrote:
It is, of course, generally considered more natural to make equality
primary, not inequality, but that symbol that MySQL uses for
NULL-safe equality,=, looks much more like inequality than
equality.
The whole concept and the name of this operator
Roberto
anybody knows how i should interpret the (null)
value in a field in order to visual.net can recognize
as just null and then make desicions?
Compare it to DBNull.Value.
PB
-
Roberto William Aranda-W Roman wrote:
hello
anybody knows how i should interpret the (null) value
hi,
You can use DBNull.
DBNull - First it returns true if such a value is null, then converts the value
to an empty string if it is null.
For instance,
If dbval Is DBNull.value then
return
End If
Thanks
ViSolve DB Team.
- Original Message -
From: Roberto William Aranda-W Roman
When doing an insert using NULL in the insert request,
what really is being written in the column?
Is the word NULL being written?
Is any real space being consumed?
In the cases that you really want to store NULL ;-)
No, it's not the word null.
Of course space is consumed.
How much
Hi!
- Original Message -
From: Martijn Tonies [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Thursday, March 16, 2006 8:59 PM
Subject: Re: NULL columns
When doing an insert using NULL in the insert request,
what really is being written in the column?
Is the word
Danny Stolle [EMAIL PROTECTED] wrote on 01/23/2006 01:34:01 PM:
Hi,
I am often running into this type of design, but would it be a good
design. Normally you design tables related to each other and having e.g.
parameter tables like a country-table holding country names with their
In the last episode (Dec 12), Eamon Daly said:
I'm sure the answer is You're SOL, but I figured I'd ask anyway. I
have a WHERE condition like:
SELECT * FROM a JOIN b WHERE
(b1 IS NULL OR b1 = u1) AND
(b2 IS NULL OR b2 = u2) AND
(b3 IS NULL OR b3 = u3)
where b is a Very Large table. I
In the last episode (Dec 12), Dan Nelson said:
In the last episode (Dec 12), Eamon Daly said:
I'm sure the answer is You're SOL, but I figured I'd ask anyway. I
have a WHERE condition like:
SELECT * FROM a JOIN b WHERE
(b1 IS NULL OR b1 = u1) AND
(b2 IS NULL OR b2 = u2) AND
(b3 IS
: mysql@lists.mysql.com
Sent: Monday, December 12, 2005 2:45 PM
Subject: Re: NULL, OR, and indexes
In the last episode (Dec 12), Dan Nelson said:
In the last episode (Dec 12), Eamon Daly said:
I'm sure the answer is You're SOL, but I figured I'd ask anyway. I
have a WHERE condition like:
SELECT
Robert Crowell wrote:
However, if the table was created with columns A, B, C, and D, most of the
entries in the D column will be NULL. Is this considered 'good form'? Even
though these entries are all NULL, they will still consume the disk space that
a DOUBLE will, correct?
I can't find it
Robert Crowell wrote:
I have data from two different sources that I would like to store in a mysql
database. Dataset_1 has pieces of information A, B, and C (let's say first,
middle, and last name) and Dataset_2 has pieces of information A, B, C, and D
(first, middle, last names and a DOUBLE
Mark Leith wrote:
From: Michael Stassen [mailto:[EMAIL PROTECTED]
snip
Something like
ORDER BY IF(col IS NULL, 1, 0), col
Michael
Or simply:
ORDER BY col IS NULL, col
Which will probably be *slightly* faster..
Good point.
Михаил Монашёв wrote:
Hello
MS Something like
MS
Scott Hamm wrote:
How do I use ORDER BY in a way that it list null last after Z instead of
before A?
I.e. instead of:
Null, A, B, C
result would be:
X, Y, Z, null
How can I get around to that?
Something like
ORDER BY IF(col IS NULL, 1, 0), col
Michael
--
MySQL General Mailing
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: 13 July 2005 13:38
To: Scott Hamm
Cc: 'Mysql '
Subject: Re: Null alphabetic order
Scott Hamm wrote:
How do I use ORDER BY in a way that it list null last after
Z instead
of before A?
I.e
Hi,
don't forget to apply the correct changes to your queries. Having NULLs or not,
let you write for example :
select ... from ... where midinials is NULL;
And be aware about NULL indexation in some storages. Those values are not
indexed for example in oracle. I'm not sure about innodb, but
Scott Purcell [EMAIL PROTECTED] wrote on 07/05/2005 04:21:06 PM:
Hello,
I have created a web-based simple application, and used mysql for
data storage. All has worked well. But I do have a simple question I
would like to ask the group.
I have some web-based forms that match table
Hi Scott,
I have created a web-based simple application, and used mysql for data
storage. All has worked well. But I do have a simple question I would like
to ask the group.
I have some web-based forms that match table structure. Most of the
important fields have validation, and I ensure good
Rachael LaPorte Taylor wrote:
I'm trying to import a file using LOAD DATA INFILE into a table
containing columns that default to NULL.
See http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html :
| Handling of NULL values varies according to the FIELDS and LINES
options in use:
|
| * For the
For certain columns i am completely sold on not using nulls, for others, i
cant see any conceptual reason to favor either way, so i thought i'd tap
you all for some insight.
A NULL is the equivalent of saying I don't know whereas a 0 means the value
between -1 and 1 and a blank means 'no
Try
SELECT * FROM table ORDER BY col LIMIT 23 ;
If the col type is numeric, trying to match the string NULL might be the
problem. And, if it is numeric, the NULL values will be listed first in
'ORDER BY col'.
This doesn't really solve the problem of not being able to select 'where
col is
It might help if you say what version of MySQL you're using, give the
table schema, etc. I couldn't reproduce the behavior you describe
here.
--Pete
On Mon, Jul 19, 2004 at 12:13:47PM -0500, Deepak Vishwanathan wrote:
Hi,
I have a table with a column that has the Unique key constraint
With NULL you can check it usign the IS NULL operator.
NULL could be a different use from the programer, like a row that IS NULL is
a ROW that never have a data in that column, but a row with no data could be
a row that was changed to that value.
You could define the default value when is
.
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 17, 2003 8:44 AM
To: Knepley, Jim
Cc: [EMAIL PROTECTED]
Subject: RE: Null-safe equal help, please
Please reply to the list so that others can follow this discussion.
Thanks.
At 8:26 -0700 12/17/03, Knepley, Jim
Please reply to the list so that others can follow this
discussion. Thanks.
At 8:26 -0700 12/17/03, Knepley, Jim wrote:
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 5:44 PM
To: Knepley, Jim; [EMAIL PROTECTED]
Subject: Re: Null-safe
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 5:44 PM
To: Knepley, Jim; [EMAIL PROTECTED]
Subject: Re: Null-safe equal help, please
At 15:22 -0700 12/16/03, Knepley, Jim wrote:
I've got a WHERE clause:
WHERE possibly_null_value IS NULL
At 15:22 -0700 12/16/03, Knepley, Jim wrote:
I've got a WHERE clause:
WHERE possibly_null_value IS NULL
That works fine. This null-safe equal doesn't do what I expect:
WHERE possibly_null_value = NULL
The manual, and my testing, shows that NULL = NULL evaluates to 1, so
Are you saying that this is
At 11:12 AM -0400 9/15/03, Martin Gainty wrote:
Randy-
NULL means No Data
So in the case of a MySQL Column defined as Type String
NULL is equivalent to
Actually, it's not.
isn't No Data, it's Data with a length of zero.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB,
PROTECTED]; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, September 15, 2003 5:02 PM
Subject: Re: NULL
: At 11:12 AM -0400 9/15/03, Martin Gainty wrote:
: Randy-
:
: NULL means No Data
: So in the case of a MySQL Column defined as Type String
: NULL is equivalent to
:
: Actually, it's
PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, September 15, 2003 5:02 PM
Subject: Re: NULL
: At 11:12 AM -0400 9/15/03, Martin Gainty wrote:
: Randy-
:
: NULL means No Data
: So in the case of a MySQL Column defined as Type String
: NULL is equivalent to
:
: Actually, it's not.
:
: isn't No Data
You need a LEFT JOIN.
SELECT blah, blah, blah
FROM bite bite LEFT JOIN person agency ON agency.person_id =
bite.bite_agency_id
WHERE bite.bite_id = AC-2003-0004;
Check out http://www.mysql.com/doc/en/JOIN.html to learn more about LEFT
JOIN.
Regards,
Mike Hillyer
www.vbmysql.com
-Original
On Thursday 13 March 2003 20:27, Steve Holt wrote:
I am linking an MS Access front end to MYSQL on the back with ODBC
If I open the table I can enter a value in the course name field only which
is the primary key
and it will save the record even though I have not entered values in the
fields
NOT NULL does not mean you can not leave the field empty.
It means the field can not contain the value NULL
All the empty fields get the default values you declared.
If you did not declare a default value, standard default values
for each data type are used.
Steve Holt wrote:
I am linking an MS
I am linking an MS Access front end to MYSQL on the back with ODBC
If I open the table I can enter a value in the course name field only which
is the primary key
and it will save the record even though I have not entered values in the
fields set up as NOT NULL
I have an ODBC trace file but it
You have to show us.
Steve Holt wrote:
I am sure I am missing something very simple but I cant see it
I am using MS Access 97 as a front end to MYSQL
I have a table with the following desc
CREATE TABLE `tblcoursesoffered` (
`DateCourseCreated` datetime default NULL,
`Specialty` int(25) NOT
Scott,
Monday, December 02, 2002, 5:46:18 AM, you wrote:
SB I'm in the process of rebuilding a table in MyISAM format. I can see the
SB plaintext data in the .MYD file just fine. But after running myisamchk
SB -i table, which reports fine and repairs the index to the correct 388
SB rows... I go
]]
Sent: Monday, December 02, 2002 7:18 AM
To: [EMAIL PROTECTED]
Subject: re: Null data after table rebuild
Scott,
Monday, December 02, 2002, 5:46:18 AM, you wrote:
SB I'm in the process of rebuilding a table in MyISAM format. I can see
the
SB plaintext data in the .MYD file just fine. But after
Artem,
Thursday, October 24, 2002, 7:34:02 PM, you wrote:
AK Description:
AK Probably it's variation of recent bug with null joins, but just in case I want to
post it here because result depends on values inserted into tables, even if these
values are not participating in
AK join.
AK After data
jfield,
Thursday, October 10, 2002, 1:33:10 AM, you wrote:
jazdc Description:
jazdc After adding a key to a nullable column, null values will
jazdc successfully join through to null values in other tables.
jazdc This only seems to happen when the index is added after
jazdc the row contains null
jfield,
Thursday, October 10, 2002, 1:33:10 AM, you wrote:
jazdc After adding a key to a nullable column, null values will
jazdc successfully join through to null values in other tables.
jazdc This only seems to happen when the index is added after
jazdc the row contains null values. This
From: [EMAIL PROTECTED]
After adding a key to a nullable column, null values will
successfully join through to null values in other tables.
And this is a problem because? :-)
Seriously, it may be a bug, but IMHO it is a design flaw to index a NULL field.
Indexed fields should always be NOT
: Thursday, October 10, 2002 9:45 AM
To: [EMAIL PROTECTED]
Subject: Re: NULL sometimes joins to NULL
From: [EMAIL PROTECTED]
After adding a key to a nullable column, null values will
successfully join through to null values in other tables.
And this is a problem because? :-)
Seriously, it may be a bug
It looks like a bug. I was able to repeat it and I had different results for the same
join depending on when index was added and values of actual data in tables.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 09, 2002 6:33 PM
To: [EMAIL
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Chuck,
If you need to change NULLs to a value all you need do is:
UPDATE table_name SET field_name = 'your_value' WHERE field_name IS
NULL;
HTH,
Bryant Hester
- -Original Message-
From: Chuck PUP Payne [mailto:[EMAIL PROTECTED]]
Sent:
On 28 Aug 2002, at 19:49, Mertens Bram wrote:
Here are a few lines from the pet.txt:
flufyyharold cat f 1993-02-04 \n
Chirpy\n bird\n \n \n
[snip]
What am I doing wrong?
Not paying attention to case. As you said earlier, the code for
In addition to my previous post, if I use the INSERT command like:
mysql INSERT INTO pet VALUES ('bark',NULL,'dog','m','2000-01-30',NULL);
the NULL values are entered correctly:
mysql SELECT * FROM pet;
+--++-+--+++
| name | owner | species
On Wed, 2002-08-28 at 21:17, Keith C. Ivey wrote:
Not paying attention to case. As you said earlier, the code for NULL
is \N, not \n.
Oh my! I feel so stupid! I should have known to check the case better by now.
\n is a newline in mysql, right? That's probably why the lines are 'broken' in
On Tue, 27 Aug 2002, Will K. wrote:
hello,
Please dont tell me to RTFM on this one (cause I am already doing that), but
for clarity's sake... can someone tell me what it means when you use NULL
and NOT NULL in a query (specifically CREATE TABLE)? Also, when should I
use them?
Will,
Pada Tue, 27 Aug 2002 01:09:32 +
Will K. [EMAIL PROTECTED] menulis :
hello,
Please dont tell me to RTFM on this one (cause I am already doing that), but
for clarity's sake... can someone tell me what it means when you use NULL
and NOT NULL in a query (specifically CREATE TABLE)?
Quinten Steenhuis wrote:
Sql, query
Hi,
I have a field that I need to do a join on. Unfortunately, the column is
allowed to be null (and it should be). My plan is to return the literal
string 'None' if the column is null, and otherwise to return the joined
value. How can I do this,
You might want to use the full join syntax here to be clear on what is
happening.
select
title as Title,
COALESCE(o.name,'None') as Organization
from m
left join o on o.ID = m.organization
Also, notice COALESCE. Much less messy.
Brian.
- Original Message -
From: Quinten Steenhuis
Mary,
Wednesday, August 14, 2002, 7:23:47 PM, you wrote:
MS here is is ...
MS 'check to see if agent/dist code are already in temp table
MS sSql = SELECT * _
MS FROM TempSap _
MS WHERE ProducerID='
ok , but I have now just programmed around the error
to meet my deadline...
-Original Message-
From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 15, 2002 6:08 AM
To: [EMAIL PROTECTED]
Subject: Re: RE: null
Mary,
Wednesday, August 14, 2002, 7:23:47 PM
That is really odd, because I don't see a select statement anywhere.
Mary Stickney wrote:
SQL QUERY SQL QUERY
I inserted a row , and set the sales to 0 for the years that there is none ,
and set the sales to the sales amount for th years
that there were some
INSERT INTO TempSap
:11 AM
To: Mary Stickney
Cc: [EMAIL PROTECTED]
Subject: Re: null
That is really odd, because I don't see a select statement anywhere.
Mary Stickney wrote:
SQL QUERY SQL QUERY
I inserted a row , and set the sales to 0 for the years that there is none
,
and set the sales to the sales amount for th
Empty strings are NOT nulls.
They are just zero length strings.
Aron Pilhofer wrote:
Changing the column to allow NULL doesn't mean that any of the existing
values will be *changed* to NULL. That seems to be what you're expecting,
but why?
No, no. Maybe I didn't explain well enough. The
At 13:20 -0400 8/12/02, Aron Pilhofer wrote:
Hey folks. I was getting some odd and unexpected results via a web query the
other day, and I finally traced the problem down -- but it lead me to wonder
if the problem was expected or a bug. Here's the deal:
When I initially created the MySQL table,
When I initially created the MySQL table, I forgot to specify that I
wanted to allow NULL in certain fields. I populated the table,
realized my mistake and then changed the field to allow NULLs. Here's
where things got weird: When I ran a select..where field is not
NULL against one of the
Changing the column to allow NULL doesn't mean that any of the existing
values will be *changed* to NULL. That seems to be what you're expecting,
but why?
No, no. Maybe I didn't explain well enough. The fields in question contained
NULL values from the beginning, when I populated them, even
sql,query
The current
behavior is what I'd expect to have happen, and what I'd expect most
people to expect to have happen. Maybe you just need to adjust your
expectations.
Paul/Keith:
OK, I see. It was a varchar field, so I assume it must have been entering
empty strings where
Arul,
Tuesday, June 04, 2002, 9:23:34 AM, you wrote:
A I have created a table parent and also another table child where parentid
A column in child is a foreign key to the id column in parent table
A Is it possible that i have no entries in parent table , and i am inserting
A null values
At 16:10 -0800 3/7/02, Shankar Unni wrote:
I tried setting a TIMESTAMP column (nullable, not first timestamp in
that table) in mysql 3.23.38) to NULL, but it seems to get the value
00. Is it not possible to set a timestamp column to
NULL?
It is not possible. Setting a TIMESTAMP
Shankar Unni wrote:
I tried setting a TIMESTAMP column (nullable, not first timestamp in
that table) in mysql 3.23.38) to NULL, but it seems to get the value
00. Is it not possible to set a timestamp column to NULL?
It's even worse: if you explicitly insert the value NULL (as
At 16:31 -0800 3/7/02, Shankar Unni wrote:
Shankar Unni wrote:
I tried setting a TIMESTAMP column (nullable, not first timestamp
in that table) in mysql 3.23.38) to NULL, but it seems to get the
value 00. Is it not possible to set a timestamp column
to NULL?
It's even worse: if
Paul DuBois wrote:
Feature you missed. Have a look here:
http://www.mysql.com/doc/D/A/DATETIME.html
Thanks. I wonder how I missed that.
Of course, the page lies somewhat: it says that if you omit the column in
an insert, it should get set to now(), but the following example shows it
Of course, the page lies somewhat: it says that if you omit the column in
an insert, it should get set to now(), but the following example shows it
doesn't - notice that u is omitted in the insert, but gets set to 0
instead of now().
create table foo (t timestamp, u timestamp);
Guess you still missed this line:
- If you have multiple TIMESTAMP columns, only the first one is updated
- automatically.
Your example explicitly sets the first one to an empty string and doesn't
set the second, so both get 000...
Michael
On Thu, 7 Mar 2002, Shankar Unni wrote:
Paul
On 27 Feb 2002, at 8:18, Baines, Dominic wrote:
If you immediately use the dump, this is fine if it is to be used
with MySQL but if you want to port it to another RDBMS the /N for
a NULL causes some 'issues'.
If you don't care about the difference between NULL and the empty
string or 0, why
:[EMAIL PROTECTED]]
Sent: 27 February 2002 15:12
To: [EMAIL PROTECTED]
Subject: Re: NULL Values in mysqldump
On 27 Feb 2002, at 8:18, Baines, Dominic wrote:
If you immediately use the dump, this is fine if it is to be used
with MySQL but if you want to port it to another RDBMS the /N for
a NULL
On 27 Feb 2002, at 10:31, Baines, Dominic wrote:
However, in this case the MySQL columns values are NULL the problem
(if it is one) is that /N does not mean NULL in other RDBMS and you have
to craft a method of converting or parsing the data either at load or
pre-load to another format to
Try IFNULL
HTH
Dan Crawford
Integrated Network Strategies
-Original Message-
From: SankaraNarayanan Mahadevan [mailto:[EMAIL PROTECTED]]
Sent: Sunday, December 30, 2001 9:52 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Null Value In a Column
Hi,
I want to select record
SELECT * FROM classification
WHERE level1 = FOO AND level2 IS NULL;
IS NULL instead of = NULL
null is not a value, it's the abscence of value
Etienne
Ward, Mark wrote:
I don't know how this affects anything, but, I'm running the most recent
version of mySQL for win98.
Why is it that an
GUYOT Carole writes:
I'm trying to insert records from a file. These records are separated
with pipes.
load data local infile aid.exp into table aid;
How is MySQL supposed to know that you're using a
non-standard way of separating your records if you don't
tell it? :-O
You need to specify
At 10:17 AM +1000 7/26/01, Alan Tse wrote:
I have a date field, how do I check if it is null or
empty in sql statement.
I tried :
select *
where fDate = NULL;
Wrong test. NULL is special. Use:
where fDATE IS NULL
and also tried
where fDate = '';
Both return nothing but actually there
[EMAIL PROTECTED] wrote:
Stefan,
When you insert a row in a table and you do not specify a value
for a field, mySQL (and any other DBMNS?) will do the following:
1. If field accepts null: it will set the value of that field
to null
2. If field DOES NOT accept null - no default value
If you just reply to this message, and include the entire text of it in
the
reply, your reply will go through. However, you should
first review the text of the message to make sure it has something to do
with MySQL. You have written the following:
hi,
j.urban wrote:
Your statement
At 8:21 PM +0200 5/3/01, stefan mojschewitsch wrote:
hi list,
i have a problem with DEFAULT NULL value in a varchar field.
mysql is version 3.23.33.
i want to have an user field in a row, to see who has created the row.
create table minidb.tel (
id int UNSIGNED NOT
nevermind...found it...ifnull()
thanks anyway!
Kris Gonzalez wrote:
i remember coming across the mysql equivalent of the oracle nvl()
function which will substitute in the case of a returned null value,
but
i can't seem to locate the command now...anyone know what it is?
thanks!
Saludos.
Creo que soy el nico hispano y necesito de ustedes.
Construyo la siguiente consulta.
Select * from coniarticulos
left join conpproveedores use index (prov_codigo) on
prov_codigo=arti_proveedor
left join conimarcas use index (marc_codigo) on marc_codigo=arti_marca and
80 matches
Mail list logo
