Hi Daniel,
Could you check the 'myview' once again? I think you thought to create the
view as follows:
" CREATE VIEW `myview2` AS SELECT a.*, EMPTY_STRING(b.`Name`) AS
`TypeName` FROM `mytable` a LEFT JOIN `types` b ON *a.ID* *= b.`ID`*;
"
Now your select queries will give results as follows:
mysql> SELECT COUNT(*) FROM `myview2` WHERE `TypeName` LIKE '%';
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM `myview2` WHERE `TypeName` IS NOT NULL;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM `myview2` WHERE `TypeName` IS NULL;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
Regards,
Vinodh.k
MySQL DBA
On Sat, Apr 23, 2011 at 1:50 AM, Daniel Kraft <d...@domob.eu> wrote:
> Hi Carsten,
>
>
> On 04/22/11 22:11, Carsten Pedersen wrote:
>
>> On 22.04.2011 21:37, Daniel Kraft wrote:
>>
>>> DROP DATABASE `test`;
>>> CREATE DATABASE `test`;
>>> USE `test`;
>>>
>>> CREATE TABLE `mytable`
>>> (`ID` SERIAL,
>>> `Type` INTEGER UNSIGNED NULL,
>>> PRIMARY KEY (`ID`));
>>> INSERT INTO `mytable` (`Type`) VALUES (NULL);
>>>
>>> CREATE TABLE `types`
>>> (`ID` SERIAL,
>>> `Name` TEXT NOT NULL,
>>> PRIMARY KEY (`ID`));
>>> INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
>>>
>>> DELIMITER |
>>> CREATE FUNCTION `EMPTY_STRING` (value TEXT)
>>> RETURNS TEXT
>>> DETERMINISTIC
>>> BEGIN
>>> RETURN IF(value IS NULL, '', value);
>>> END|
>>> DELIMITER ;
>>>
>>> CREATE VIEW `myview` AS
>>> SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
>>> FROM `mytable` a
>>> LEFT JOIN `types` b ON a.`Type` = b.`ID`;
>>>
>>> SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
>>> SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
>>> SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
>>>
>>> (I tried to simplify my problem as far as possible.) When I run this
>>> against MySQL 5.0.24a, I get three times "0" as output from the SELECTs
>>> at the end -- shouldn't at least one of them match the single row?
>>> (Preferably first and third ones.)
>>>
>>> What am I doing wrong here? I have no clue what's going on... Thanks a
>>> lot!
>>>
>>
>> Hint: What's the output of "SELECT * FROM `myview`?
>>
>
> I get:
>
> mysql> select * from myview;
> +----+------+----------+
> | ID | Type | TypeName |
> +----+------+----------+
> | 1 | NULL | |
> +----+------+----------+
> 1 row in set (0.00 sec)
>
> mysql> select *, `TypeName` IS NOT NULL from myview;
> +----+------+----------+------------------------+
> | ID | Type | TypeName | `TypeName` IS NOT NULL |
> +----+------+----------+------------------------+
> | 1 | NULL | | 1 |
> +----+------+----------+------------------------+
> 1 row in set (0.00 sec)
>
> Should this tell me something? To me, it looks as expected and fine.
>
> Cheers,
>
> Daniel
>
> --
> http://www.pro-vegan.info/
> --
> Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
> To go: Hea-Mon-Pri
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=daffodil...@gmail.com
>
>
