At 14:36 04/02/2003 +0200, Ciprian Trofin wrote: Hi, I tested against the last release 3.23.55/4.0.10 and below the results:
c:\mysql\bin>mysql -uroot -p test Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.55-max-debug ....... Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT R.id, U.user FROM ring R, users U WHERE R.user = U.id; +----+------+ | id | user | +----+------+ | 4 | cip | | 1 | gogu | | 3 | gogu | | 5 | gogu | | 2 | alex | +----+------+ 5 rows in set (0.05 sec) mysql> SELECT R.id, U.user FROM users U, ring R WHERE R.user = U.id; +----+------+ | id | user | +----+------+ | 1 | gogu | | 2 | alex | | 3 | gogu | | 4 | cip | | 5 | gogu | +----+------+ 5 rows in set (0.01 sec)
I fixed the problem w/ the indices, but it still doesn't work. Same probl.
g> You have some unnecessary indicies.
g> What happens if you remove them?
>>I am using MySQL 3.23.47-nt on Windows 2000 Pro SP3;
>>
>>I have the following DB's:
>>
>># Host : localhost
>># Database : test
>>
>>CREATE DATABASE test;
>>USE test;
>>
>># Structure for table users :
>>
>>CREATE TABLE `users` (
>>`id` smallint(4) NOT NULL auto_increment,
>>`user` varchar(8) NOT NULL default '',
>>PRIMARY KEY (`id`),
>>UNIQUE KEY `user` (`user`),
>>UNIQUE KEY `id` (`id`)
>>) TYPE=MyISAM;
>>
>>
g> You have a duplicate index for 'id'
>># Structure for table ring :
>>
>>CREATE TABLE `ring` (
>>`id` smallint(6) NOT NULL auto_increment,
>>`user` smallint(6) NOT NULL default '0',
>>PRIMARY KEY (`id`),
>>UNIQUE KEY `if` (`id`)
>>) TYPE=MyISAM;
>>
>>
g> You have a duplicate key for 'id'.
>># Data for table ring
>>
>>INSERT INTO `ring` (`id`, `user`) VALUES
>>('1','2'),
>>('2','3'),
>>('3','2'),
>>('4','1'),
>>('5','2');
>>
>># Data for table users
>>
>>INSERT INTO `users` (`id`, `user`) VALUES
>>('1','cip'),
>>('2','gogu'),
>>('3','alex');
>>
>>################################
>>
>>Now, the problem:
>>
>>This doesn't work:
>>SELECT R.id, U.user FROM ring R, users U WHERE R.user = U.id;
>>
>> id user
>> -------------
>> 4 0
>> 1 0
>> 3 0
>> 5 0
>> 2 0
>>
>>
>>This works:
>>SELECT R.id, U.user FROM users U, ring R WHERE R.user = U.id;
>>
>> id user
>> -----------
>> 1 gogu
>> 2 alex
>> 3 gogu
>> 4 cip
>> 5 gogu
>>
>>
>>I guess it is some sort of conflict, although it shouldn't be.
--
Ciprian
> Un cuvant de sfarsit:
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Miguel Angel Sol�rzano <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ S�o Paulo - Brazil
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
