table three MySQL

[Andrew]

Hi MySQL SQL

I think I need to say this out loud and hopefully someone will read it ans say
stop you are doing this all wrong!  Or wait a minute just do this :)

I am trying to display records (which was previously happening with just one
table!) from table 'items'

The search is based on category and city

The category is in the 'items' table and the city is in the 'item_city' table.

I have just thought where a problem could lie.  The city is selected from a link
table city.  But the city_id for each item is in the item_city table and it is
here I am trying to get the search/filter to work.

so the tables look like this:

CREATE TABLE city (
  CountyID int(10) unsigned NOT NULL default '0',
  City varchar(100) NOT NULL default '',
  CityID bigint(20) NOT NULL auto_increment,
  MoPID int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (CityID),
  KEY CountyID (CountyID),
  KEY MoPID (MoPID)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `item_city`
#

CREATE TABLE item_city (
  id int(11) NOT NULL auto_increment,
  item_id int(11) default NULL,
  city_id int(11) default NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `items`
#

CREATE TABLE items (
  ItemSKU varchar(25) NOT NULL default '',
  ItemName varchar(100) NOT NULL default '',
  ItemDescription mediumtext NOT NULL,
  PostCode varchar(100) NOT NULL default '',
  Category bigint(20) NOT NULL default '0',
  CTelephone varchar(100) NOT NULL default '',
  ItemID bigint(20) NOT NULL auto_increment,
  Cfax varchar(100) NOT NULL default '',
  Cemail varchar(200) NOT NULL default '',
  Caddress varchar(200) NOT NULL default '',
  CTown varchar(200) NOT NULL default '',
  Cwww varchar(200) NOT NULL default '',
  PRIMARY KEY  (ItemID)
) TYPE=MyISAM;

the query looks like this:

SELECT items.ItemSKU
     , items.ItemName
     , items.ItemDescription
     , items.PostCode
     , items.Category
     , items.CTelephone
     , items.ItemID
     , items.Cfax
     , items.Cemail
     , items.Caddress
     , items.CTown
     , items.Cwww
     , item_city.item_id
     , item_city.city_id
  FROM items
left OUTER
  JOIN item_city
    ON items.ItemID = item_city.item_id
   AND item_city.city_id = '$city_id'
 WHERE item_city.city_id = '$city_id'
order
    by items.ItemID

and the result just pulls the category regarless of what city


Can anyone see my foolish ways??

I think this has something to do with the city table?  Maybe not maybe the query
is just not right.

When I had the CityID in the items table everything was fine and this worked:

SELECT items.ItemSKU, items.ItemName, items.ItemDescription, items.PostCode,
items.Category, items.CTelephone, items.ItemID, items.Cfax, items.Cemail,
items.Caddress, items.CTown, items.Cwww FROM items Inner Join item_city ON
item_city.city_id= items.ItemID WHERE items.Category='$Category' and
item_city.city_id='$ItemID'

but now that I have become a DB design guru and dropped the CityID from items
the new query doesn't want to recognise my other tables!!


Andrew


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]