Actually thinking about this in a bit more depth! I also have the infamous items table where individual company records are being stored. So is the best way to do this:
i) add another field that will coreespond to the items.ItemsID or ii) create another table town_ref CREATE TABLE town_ref ( town_id int(11) DEFAULT '' NOT NULL auto_increment, town varchar(255) , PRIMARY KEY (town_id) ); and then another table: CREATE TABLE item_town ( id int(11) DEFAULT '' NOT NULL auto_increment, item_id int(11) , town_id int(11) , PRIMARY KEY (id) ); which will then involve adding another column to the items table :( Andrew >-----Original Message----- >From: Roger Baklund [mailto:[EMAIL PROTECTED] >Sent: 07 March 2003 14:51 >To: MySQL-Lista >Cc: [EMAIL PROTECTED] >Subject: Re: another Table another query coming soon > > >* Andrew >> I am going to add another table to my lovely MySQL DB. >> >> Basically it is the city table I need to add to. For each city I >> am going to >> have Towns. So I need to relate many Towns to one CityID >> >> The city table at the moment is: >> >> City CityID >> >> What the best way forward? > >Create a Towns table, and link it to the Cities table? > >CREATE TABLE Towns ( > TownID int unsigned not null auto_increment primary key, > CityID int unsigned not null, > Town varchar(40) not null, > index (CityID), > index (Town(8))); > >Show all towns for a city: > >SELECT Town > FROM Towns > NATURAL JOIN Cities > WHERE Cities.City = "London"; > >or > >SELECT T.Town > FROM Towns T > LEFT JOIN Cities C ON > C.CityID = T.CityID > WHERE C.City = "London"; > >HTH, > >-- >Roger > >--- >Incoming mail is certified Virus Free. >Checked by AVG anti-virus system (http://www.grisoft.com). >Version: 6.0.459 / Virus Database: 258 - Release Date: 25/02/2003 > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
