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...@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
--
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=arch...@jab.org
