From: Peter Brawley Date: October 19 2005 2:10pm Subject: Re: Multiple INNER JOINS List-Archive: http://lists.mysql.com/mysql/190571 Message-Id: <435653D7.4010303@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-435653D76868=======" --=======AVGMAIL-435653D76868======= Content-Type: multipart/alternative; boundary=------------050305060808020400080309 --------------050305060808020400080309 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Shaun, />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? PB ----- Shaun wrote: >Hi, > >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. > >SELECT P.*, >Tax_Band, >Property_Type >FROM Properties P >INNER JOIN Tax_Bands USING(Tax_Band_ID) >INNER JOIN Property_Types USING(Property_Type_ID) >WHERE P.Property_ID = 3 > >Here is the error message I am getting: > >Unknown column 'Tax_Bands.Property_Type_ID' in 'on clause' > >Thanks for your advice. > ># ---------- MySQL dump ---------- ># ># Table structure for table 'Properties' ># >CREATE TABLE Properties ( > Property_ID int(11) DEFAULT '' NOT NULL auto_increment, > Property_Name_Or_Number varchar(50) , > Address_Line_1 varchar(50) , > Address_Line_2 varchar(50) , > City varchar(50) , > County varchar(50) , > Postcode varchar(12) , > Sale_Price int(11) , > Asking_Price int(11) , > Years_On_Lease int(11) , > Tax_Band_ID int(11) , > Property_Type_ID int(11) , > Number_Of_Bedrooms int(11) , > Number_Of_Bathrooms int(11) , > Number_Of_Receptions int(11) , > Internal_Square_Footage int(11) , > Internal_Square_Meters int(11) , > Price_Per_Square_Foot decimal(6,2) , > Price_Per_Square_Meter decimal(6,2) , > Additional_Notes text , > PRIMARY KEY (Property_ID) >); > ># ># Table structure for table 'Property_Types' ># >CREATE TABLE Property_Types ( > Property_Type_ID int(11) DEFAULT '' NOT NULL auto_increment, > Property_Type varchar(50) , > PRIMARY KEY (Property_Type_ID) >); > ># ># Table structure for table 'Tax_Bands' ># >CREATE TABLE Tax_Bands ( > Tax_Band_ID int(11) DEFAULT '' NOT NULL auto_increment, > Tax_Band varchar(50) , > PRIMARY KEY (Tax_Band_ID) >); > ># ----------- Dump ends ----------- > > > > > --------------050305060808020400080309 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Shaun,

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

PB

-----

Shaun wrote:

Hi,

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.

SELECT P.*,
Tax_Band,
Property_Type
FROM Properties P
INNER JOIN Tax_Bands USING(Tax_Band_ID)
INNER JOIN Property_Types USING(Property_Type_ID)
WHERE P.Property_ID = 3

Here is the error message I am getting:

Unknown column 'Tax_Bands.Property_Type_ID' in 'on clause'

Thanks for your advice.

# ---------- MySQL dump ----------
#
# Table structure for table 'Properties'
#
CREATE TABLE Properties (
  Property_ID int(11)  DEFAULT '' NOT NULL auto_increment,
  Property_Name_Or_Number varchar(50)    ,
  Address_Line_1 varchar(50)    ,
  Address_Line_2 varchar(50)    ,
  City varchar(50)    ,
  County varchar(50)    ,
  Postcode varchar(12)    ,
  Sale_Price int(11)    ,
  Asking_Price int(11)    ,
  Years_On_Lease int(11)    ,
  Tax_Band_ID int(11)    ,
  Property_Type_ID int(11)    ,
  Number_Of_Bedrooms int(11)    ,
  Number_Of_Bathrooms int(11)    ,
  Number_Of_Receptions int(11)    ,
  Internal_Square_Footage int(11)    ,
  Internal_Square_Meters int(11)    ,
  Price_Per_Square_Foot decimal(6,2)    ,
  Price_Per_Square_Meter decimal(6,2)    ,
  Additional_Notes text    ,
  PRIMARY KEY (Property_ID)
);

#
# Table structure for table 'Property_Types'
#
CREATE TABLE Property_Types (
  Property_Type_ID int(11)  DEFAULT '' NOT NULL auto_increment,
  Property_Type varchar(50)    ,
  PRIMARY KEY (Property_Type_ID)
);

#
# Table structure for table 'Tax_Bands'
#
CREATE TABLE Tax_Bands (
  Tax_Band_ID int(11)  DEFAULT '' NOT NULL auto_increment,
  Tax_Band varchar(50)    ,
  PRIMARY KEY (Tax_Band_ID)
);

# ----------- Dump ends -----------

--------------050305060808020400080309-- --=======AVGMAIL-435653D76868======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.3/141 - Release Date: 10/18/2005 --=======AVGMAIL-435653D76868=======--