Hi, thanks for the fast reply!
On 04/22/11 21:39, Daevid Vincent 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!Try this maybe: SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NULL; SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NOT NULL; SELECT COUNT(*) FROM `myview` HAVING `TypeName` LIKE '%';
When I try those, I get: ERROR 1054 (42S22) at line 35: Unknown column 'TypeName' in 'having clause' What would be the difference? (I've never used HAVING before.) Yours, 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/[email protected]
