Hi!
>>>>> "Egbert" == Egbert Engel <Egbert.Engel@stripped>
> writes:
Egbert> Hi,
Egbert> I have a problem when joining two tables (table1, table2).
Egbert> Table 1's primary key column also exists in table2 as "foreign key" column
Egbert> (I know that there actually are no foreign keys in MySQL).
I assume you also know that foreign keys has nothing to do with
joining tables in a SELECT statement!
Egbert> I use the following join syntax:
>> select * from table1 natural left join table2;
Egbert> which returns the complete result of a regular join, i.e. without using
Egbert> the constraint of two columns with same name.
Egbert> The following, however:
>> select * from table2 natural left join table1;
Egbert> returns the correct result .
Egbert> I checked the other possibilities (LEFT JOIN ON..., LEFT JOIN
Egbert> USING...), too, with the same result. CROSS JOIN... works, of course, but
Egbert> I don't want to use it.
Egbert> I've never had this problem before and really NO idea what is wrong (or
Egbert> maybe I'm too stupid!). Would be very nice If somebody gave me a
Egbert> hint.
Which MySQL version are you using?
Could you post a full example that shows this?
Here is a test that shows that it works (at least for me:)
mysql> CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (1,'a',1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (2,'b',1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (3,'c',2);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (1,'x');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (2,'y');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (3,'z');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
+-----+
| id2 |
+-----+
| 3 |
+-----+
1 row in set (0.03 sec)
mysql> SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
+-----+------+------+------+------+
| id2 | dat2 | id1 | dat1 | id2 |
+-----+------+------+------+------+
| 1 | x | 1 | a | 1 |
| 1 | x | 2 | b | 1 |
| 2 | y | 3 | c | 2 |
| 3 | z | NULL | NULL | NULL |
+-----+------+------+------+------+
Regards,
Monty
