Hi,
thanks for the answer!
On 04/23/11 11:33, ars k wrote:
> 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`*;
> "
Hm, no, I don't think so -- what I want to achieve is to link the
Type-field (as index) in mytable to the corresponding entry in types, so
that I can get the name of this type (and other data in my real
application of course).
What you propose compares the ID of entries in mytable (customers, say)
to IDs of types, which doesn't make much sense to me. Or did I get this
wrong? (As I said, I'm more of less learning-by-doing and no expert!)
Yours,
Daniel
>
> 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@stripped> 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=1
>>
>>
>
--
http://www.pro-vegan.info/
--
Done: Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
