From: Peter Brawley Date: October 19 2005 3:02pm Subject: Re: Multiple INNER JOINS List-Archive: http://lists.mysql.com/mysql/190576 Message-Id: <43565FFC.3090100@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-43565FFC69EB=======" --=======AVGMAIL-43565FFC69EB======= Content-Type: multipart/alternative; boundary=------------070804020003020609090403 --------------070804020003020609090403 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi Shaun, I don't have a 3.23 installation to test it on just now. If Brent's suggestion fails, try changing the join pattern to tb INNER JOIN p INNER JOIN pt (if you can't beat it, join it :-) ). PB ----- Shaun wrote: >Hi Peter, > >I am using version 3.23.54, unfortunately I have no control over this :( > > >"Peter Brawley" wrote in message >news:435653D7.4010303@stripped... >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 ----------- > > > > > > > >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 > > > > > --------------070804020003020609090403 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi Shaun,

I don't have a 3.23 installation to test it on just now. If Brent's suggestion fails, try changing the join pattern to
tb INNER JOIN p INNER JOIN pt (if you can't beat it, join it :-) ).

PB

-----

Shaun wrote:

Hi Peter,

I am using version 3.23.54, unfortunately I have no control over this :(


"Peter Brawley" <peter.brawley@stripped> wrote in message 
news:435653D7.4010303@stripped...
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 ----------- 







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

--------------070804020003020609090403-- --=======AVGMAIL-43565FFC69EB======= 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.4/143 - Release Date: 10/19/2005 --=======AVGMAIL-43565FFC69EB=======--