ok this is making sense and I should have thought about this before* I created my city table as now I have a city table that in effect is useless and comlicated the existing queries :(
A lesson to be learnt indeed. >-----Original Message----- >From: Bruce Feist [mailto:[EMAIL PROTECTED] >Sent: 19 March 2003 17:59 >Cc: MySQL-Lista >Subject: Re: another table to query! > > >Andrew wrote: > >>I have done this and have now started to populate the items_city >table with the >>appropriate records what I now need to do is show the records using the city >>items_table. At the moment the records are displayed when a city is >selected bu >>the cityID is from the items table. Like this: >> >>$result=mysql_query("SELECT items.ItemSKU, items.ItemName, >>items.ItemDescription, items.PostCode, items.Category, items.CityID, >>items.CTelephone, items.ItemID, items.Cfax, items.Cemail, items.Caddress, >>items.CTown, items.Cwww FROM items WHERE Category='$Category' and >>CityID='$CityID' ORDER BY CityID"); >> >>Presumably I need to do a JOIN ? >> >> >Yes. It should look something like: > >SELECT i.ItemSKU, i.ItemName, i.ItemDescription, i.PostCode, >i.Category, ic.CityID, i.CTelephone, i.ItemID, i.Cfax, i.Cemail, >i.Caddress, i.CTown, i.Cwww >FROM items i Inner Join item_city ic ON ic.ItemID = i.ItemID >WHERE i.Category='$Category' and ic.CityID='$CityID' >ORDER BY ic.CityID > >Note that if you're just looking for a single CityID, there's no reason >to ORDER BY it, and little reason to SELECT it. > >Also, now that you have the item_city table, you can and should remove >city_id from items. If you have a significant amount of data already >entered, you might want to copy the cityid/itemid information from items >into item_city first. > >Bruce Feist > > > >--------------------------------------------------------------------- >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 > > >--- >Incoming mail is certified Virus Free. >Checked by AVG anti-virus system (http://www.grisoft.com). >Version: 6.0.463 / Virus Database: 262 - Release Date: 17/03/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
