On Fri, 2002-07-12 at 22:41, Richard Clarke wrote: > So this works as expected, > > mysql> create table test_1 (id int,value char); > Query OK, 0 rows affected (0.00 sec) > > mysql> create table test_2 (id int,val char); > Query OK, 0 rows affected (0.00 sec) > > mysql> insert into test_1 values (1,a),(2,b),(3,c); > ERROR 1054: Unknown column 'a' in 'field list' > mysql> insert into test_1 values (1,'a'),(2,'b'),(3,'c'); > Query OK, 3 rows affected (0.00 sec) > Records: 3 Duplicates: 0 Warnings: 0 > > mysql> insert into test_2 values (2,'d'),(3,'e'),(4,'f'); > Query OK, 3 rows affected (0.00 sec) > Records: 3 Duplicates: 0 Warnings: 0 > > mysql> select * from test_1 NATURAL JOIN test_2; > +------+-------+------+------+ > | id | value | id | val | > +------+-------+------+------+ > | 2 | b | 2 | d | > | 3 | c | 3 | e | > +------+-------+------+------+ > 2 rows in set (0.00 sec) > > ------------------ > > But what is wrong with this: > > CREATE TABLE `items` ( > `item_id` int(11) NOT NULL auto_increment, > `centro_id` int(11) NOT NULL default '0', > `name` varchar(50) default NULL, > `description` varchar(255) default NULL, > `url` varchar(255) default NULL, > `directory_id` int(11) default NULL, > `cost` int(11) default NULL, > PRIMARY KEY (`item_id`), > KEY `directory_id` (`directory_id`) > ) TYPE=MyISAM; > > CREATE TABLE `shop_directory` ( > `directory_id` int(11) NOT NULL auto_increment, > `short_name` varchar(32) default NULL, > `long_name` varchar(128) default NULL, > `description` text, > `parent_id` int(11) NOT NULL default '0', > PRIMARY KEY (`directory_id`) > ) TYPE=MyISAM; > > INSERT INTO items VALUES (5,58,'Swim','wet','http://www.bri.com/wet',3,50); > INSERT INTO items VALUES > (4,58,'Beach','beach','http://www.bri.com/beach',3,50); > INSERT INTO items VALUES (7,58,'Bed','bed','http://www.bri.com/bed',2,60); > INSERT INTO items VALUES (9,58,'sun','sun sun sun','www.bri.com/sun',1,34); > > INSERT INTO shop_directory VALUES ('','a','aaa','aaaaa',0); > INSERT INTO shop_directory VALUES ('','b','bbb','bbbbb',1); > INSERT INTO shop_directory VALUES ('','c','ccc','ccccc',2); > > mysql> select * from items NATURAL JOIN shop_directory; > Empty set (0.00 sec) > > What gives... why isn't it joining on directory_id. Using an inner join > with where condition it works fine.. but INNER JOIN should work also.. no? > > "The NATURAL [LEFT] JOIN of two tables is defined to be semantically > equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that > names all columns that exist in both tables." > > Richard
Hi, See: "......with a USING clause that names all columns that exist in both tables." Your query is giving an empty result because it is using 2 columns that exist in both tables: directory_id and description, and the description value in both tables isn't equal. If you change the column name "description" in one of your tables it will work -- or just do the select * from items JOIN shop_directory USING (directory_id); Your first example worked because the only column used to join was the id (since you gave different names to the other column). -- Diana Soares --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
