List:General Discussion« Previous MessageNext Message »
From:Daniel Kraft Date:April 23 2011 9:41am
Subject:Re: WHERE does not work on calculated view field
View as plain text  
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
Thread
WHERE does not work on calculated view fieldDaniel Kraft22 Apr
  • RE: WHERE does not work on calculated view fieldDaevid Vincent22 Apr
    • Re: WHERE does not work on calculated view fieldDaniel Kraft22 Apr
      • RE: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyLarry McGhaw22 Apr
        • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyCarsten Pedersen22 Apr
        • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyDaniel Kraft24 Apr
          • RE: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyLarry McGhaw25 Apr
            • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyDaniel Kraft25 Apr
            • RE: WHERE does not work on calculated view field - Foundword(s) list error in the Text bodyhsv26 Apr
              • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyDaniel Kraft26 Apr
  • Re: WHERE does not work on calculated view fieldCarsten Pedersen22 Apr
    • Re: WHERE does not work on calculated view fieldDaniel Kraft22 Apr
      • Re: WHERE does not work on calculated view fieldars k23 Apr
        • Re: WHERE does not work on calculated view fieldDaniel Kraft23 Apr