[thelist] more sql help

[Tom Dell'Aringa]

Me again on this same project. Rudy was nice enough to help me with
my table structure, but I can't get this query right.

I have 3 tables (shown below). menuitem, category and subcategory.
Each menuitem has a category. I want menuitems to also be able to be
a subcategory of a category as well, so Rudy had me make the
subcategory table.

the 'subParentID' is equal to a categoryID - its a foreign key.

I have the following query that successfully lists out everything in
nice order WITHOUT the subcategories:

$result = @mysql_query("SELECT menuitem.*, category.* FROM menuitem,
category WHERE (menuitem.categoryID = category.categoryID) ORDER BY
category, item");

But I cannot seem to successfully add the subcategories...

Below are the tables:
-----------------------------------
CREATE TABLE category (
  categoryID int(11) NOT NULL auto_increment,
  category varchar(24) NOT NULL default '',
  PRIMARY KEY  (categoryID)
)
# --------------------------------------------------------
CREATE TABLE menuitem (
  menuitemID int(11) NOT NULL auto_increment,
  item varchar(225) NOT NULL default '',
  description text,
  price decimal(7,2) NOT NULL default '0.00',
  categoryID int(11) NOT NULL default '0',
  PRIMARY KEY  (menuitemID),
  FULLTEXT KEY item (item)
)
# --------------------------------------------------------

CREATE TABLE subcategory (
  subcategoryID int(11) NOT NULL auto_increment,
  subcategory varchar(25) NOT NULL default '',
  subParentID int(11) default NULL,
  PRIMARY KEY  (subcategoryID)
)

---------
TIA

Tom

=====
var me = tom.pixelmech.webDeveloper();

http://www.pixelmech.com/
http://www.maccaws.com/
[Making A Commercial Case for Adopting Web Standards]

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus – Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com