NATURAL JOIN

[Richard Clarke]

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


---------------------------------------------------------------------
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