It might be more productive to add a foreign key to the Offices and Properties tables that points to the Primary key ID of the respective PostCode in the Postcodes table.
Combined with the other suggestions, this would give you a key from the Offices and Properties tables directly into the Postcodes table, and the associated coordinates you want to match on. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 23 Jan 2006, Shaun wrote: > To: mysql@lists.mysql.com > From: Shaun <[EMAIL PROTECTED]> > Subject: Postcode Search > > Hi, > > We have a dataset of uk postcodes and their relevant > X-Coordinates and Y-Coordinates, a table of properties > (houses), a table of users and a table of offices - users > work in an office - table structures below. > > Is it possible to run a search of all properties in the > properties table that come within a certain distance of > the users postcode, currently we do this by downloading > all properties into an array and stripping out the ones > that don't come within the radius with php. > > Any advice would be greatly appreciated. > > > # ---------- MySQL dump ---------- > # > # Table structure for table 'Offices' > # > CREATE TABLE Offices ( > Office_ID int(11) DEFAULT '' NOT NULL auto_increment, > Subscriber_ID int(11) , > Type varchar(10) , > Address_Line_1 varchar(50) , > Address_Line_2 varchar(50) , > City varchar(50) , > County varchar(50) , > Postcode varchar(10) , > Telephone varchar(12) , > Fax varchar(12) , > Radius tinyint(4) , > PRIMARY KEY (Office_ID) > ); > > # > # Table structure for table 'Postcodes' > # > CREATE TABLE Postcodes ( > PCDSECT varchar(6) DEFAULT '' NOT NULL , > SORTSECT varchar(6) , > PCDDIST varchar(4) , > SORTDIST varchar(4) , > PCDAREA char(2) , > X_COORD double(7,1) unsigned , > Y_COORD double(7,1) unsigned , > PRIMARY KEY (PCDSECT) > ); > > # > # Table structure for table 'Properties' > # > CREATE TABLE Properties ( > CHK varchar(20) , > Property_ID int(11) DEFAULT '' NOT NULL auto_increment, > Insertion_Date date , > Status varchar(20) , > 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) , > PRIMARY KEY (Property_ID) > ); > > # > # Table structure for table 'Users' > # > CREATE TABLE Users ( > User_ID int(11) DEFAULT '' NOT NULL auto_increment, > Office_ID int(11) , > Type varchar(20) , > Title varchar(4) , > Firstname varchar(20) , > Lastname varchar(20) , > Password varchar(20) DEFAULT '' NOT NULL , > Email varchar(50) , > PRIMARY KEY (User_ID) > ); > > # ----------- Dump ends ----------- > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
