List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:October 20 2005 7:44am
Subject:Re: Multiple INNER JOINS
View as plain text  
On 10/19/05, Peter Brawley wrote:
>>
>> I am having problems with the following query: I am
>> trying to join Tax_Bands and Property_Types to Properties
>> but the query seems to be joning Tax_Bands to Properties.
>
> That query generates no error in 5.0.13. There have been several cascading
> join bugs, some fixed, some not. What version are you running?

This particular one was fixed in 5.0.12. The fix poses a serious
backward compatibility risk for every query using USING or NATURAL, so
unless you are exclusively developing for 5 and higher, you should not
use them.


The shortest explanation of the problem is to just try the following queries:
CREATE TABLE test (ID INT);
INSERT INTO test (ID) VALUES (1);

SELECT * FROM test t1 JOIN test t2 USING (ID);
SELECT ID FROM test t1 JOIN test t2 USING (ID);
SELECT t1.ID FROM test t1 JOIN test t2 USING (ID);
SELECT t2.ID FROM test t1 JOIN test t2 USING (ID);

SELECT * FROM test t1 NATURAL JOIN test t2;
SELECT ID FROM test t1 NATURAL JOIN test t2;
SELECT t1.ID FROM test t1 NATURAL JOIN test t2;
SELECT t2.ID FROM test t1 NATURAL JOIN test t2;

DROP TABLE test;


Writing the full join condition will indeed work.

Jochem
Thread
Multiple INNER JOINSShaun19 Oct
  • Re: Multiple INNER JOINSPeter Brawley19 Oct
    • Re: Multiple INNER JOINSJochem van Dieten20 Oct
  • Re: Multiple INNER JOINSShaun19 Oct
    • Re: Multiple INNER JOINSPeter Brawley19 Oct
  • Re: Multiple INNER JOINSBrent Baisley19 Oct