MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Churo Park Date:August 25 1999 12:11pm
Subject:Re: IF(), ISNULL(), and COUNT() problem
View as plain text  
I'm sorry to bother you again.

But compare the case number 2 and 3.

If an indexed field cannot be NULL, the result
of the number 3 will be like this:
+---------+-------+
| pcode   | count |
+---------+-------+
| kvw2000 |     1 |
| kvw2001 |     1 |
+---------+-------+ 

But it is like this:
+---------+-------+
| pcode   | count |
+---------+-------+
| kvw2000 |     1 |
| kvw2001 |     0 |
+---------+-------+

And the reference manual(7.12 Join syntax) said that
if there is no matching record for the right table in a LEFT JOIN,
a row with all columns set to NULL is used for the right table, so
you can use this fact to find record in a table that have no counterpart
in another table.

Is there some misunderstanding in my question?

Thanks in advance.

Churo


On Wed, 25 Aug 1999 sinisa@stripped wrote:

> churo@stripped writes:
>  > 
>  > >How-To-Repeat:
>  > 
>  > 1. When tr.pcode is not indexed:
>  > 
>  > mysql> SELECT tp.pcode, IF(ISNULL(tr.pcode), 0, COUNT(*)) AS count
>  > FROM tp
>  > LEFT JOIN tr ON tp.pcode = tr.pcode
>  > GROUP BY tp.pcode;
>  > 
>  > +---------+-------+
>  > | pcode   | count |
>  > +---------+-------+
>  > | kvw2000 |    28 |
>  > | kvw2001 |     0 |
>  > +---------+-------+ (as expected)
>  > 
>  > 2. When tr.pcode is indexed:
>  > 
>  > mysql> SELECT tp.pcode, IF(ISNULL(tr.pcode), 0, COUNT(*)) AS count
>  > FROM tp
>  > LEFT JOIN tr ON tp.pcode = tr.pcode
>  > GROUP BY tp.pcode;
>  > 
>  > +---------+-------+
>  > | pcode   | count |
>  > +---------+-------+
>  > | kvw2000 |    28 |
>  > | kvw2001 |     1 |
>  > +---------+-------+ (not as expected)
>  > 
>  > 3. When tr.pcode is indexed, but COUNT() is not used:
>  > 
>  > mysql> SELECT tp.pcode, IF(ISNULL(tr.pcode), 0, 1) AS count
>  > FROM tp
>  > LEFT JOIN tr ON tp.pcode = tr.pcode
>  > GROUP BY tp.pcode;
>  > 
>  > +---------+-------+
>  > | pcode   | count |
>  > +---------+-------+
>  > | kvw2000 |     1 |
>  > | kvw2001 |     0 |
>  > +---------+-------+ (as expected)
>  > 
> 
> Hi!
> 
> Everything is OK in your queries.
> 
> A field can not be NULL and be indexed. At least not until 3.23.xx
> 
> So, everything is OK.
> 
> Sinisa
> 

Thread
IF(), ISNULL(), and COUNT() problemchuro25 Aug
  • PHP3 & MySQL simple questionBarry25 Aug
    • Re: PHP3 & MySQL simple questionPaul DuBois25 Aug
      • Re: PHP3 & MySQL simple questionBarry25 Aug
  • IF(), ISNULL(), and COUNT() problemsinisa25 Aug
    • Re: IF(), ISNULL(), and COUNT() problemChuro Park25 Aug
  • Re: IF(), ISNULL(), and COUNT() problemMartin Ramsch13 Sep