MySQL - need to join three tables

Hi,
I basically want to create a new table which will be the same as items2 but also have an extra column with  `subcat` from the category2 table.

The item id from the items2 table is equal to the items_to_category.item which is equal to category2.id
So basically items2.id belongs to "Beads & Angles - Plastic" subcat, I hope that makes sense.

CREATE TABLE IF NOT EXISTS `items2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `itemName` varchar(50) DEFAULT NULL,
  `itemDesc` text,
  `image1` varchar(60) DEFAULT 'uploads/nopic.jpg',
  `image2` varchar(60) DEFAULT 'uploads/nopic.jpg',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1208 ;

--
-- Dumping data for table `items2`
--

INSERT INTO `items2` (`id`, `itemName`, `itemDesc`, `image1`, `image2`) VALUES
(819, 'Trim Mesh Anchor 3m x 3.5mm', 'Trim Mesh Anchor 3m x 3.5mm', '/uploads/item819_1.jpg', '/uploads/nopic.jpg'),
(820, 'Trim - 10mm L-Bead Tearaway', '<p>\r\n	Tearaway ''L'' Bead available in 3metre lengths</p>\r\n', '/uploads/item820_1.jpg', '/uploads/nopic.jpg'),
(821, 'Trim - Reveal 40mm  3.5m', '<p>\r\n	Render reveal trim - 40mm</p>\r\n', '/uploads/item821_1.jpg', '/uploads/nopic.jpg'),
(824, 'Trim - 50mm Starter Bead 3m', '<p>\r\n	Starter Trim for polystyrene - 50mm</p>\r\n', '/uploads/item824_1.jpg', '/uploads/nopic.jpg'),
(825, 'Trim-75mm Starter Bead 3m', '<p>\r\n	Starter Trim for polystyrene - 75mm</p>\r\n', '/uploads/item825_1.jpg', '/uploads/nopic.jpg'),
(826, 'Trim- 100mm Starter Bead 3m', '<p>\r\n	Starter Trim for polystyrene&nbsp;- 100mm</p>\r\n', '/uploads/item826_1.jpg', '/uploads/nopic.jpg'),


CREATE TABLE IF NOT EXISTS `items_to_category` (
  `category` int(11) NOT NULL,
  `item` int(11) NOT NULL,
  PRIMARY KEY (`category`,`item`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



INSERT INTO `items_to_category` (`category`, `item` ) VALUES
(157, 819 ),
(157, 820 ),
(157, 821 ),
(157, 822 ),
(157, 823 ),
(157, 824 ),
(157, 825 ),
(157, 826 ),
(157, 827 ),
(157, 828),
(164, 1207);


CREATE TABLE IF NOT EXISTS `category2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subcat` varchar(50) NOT NULL,
  UNIQUE KEY `id_2` (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=176 ;

--
-- Dumping data for table `category2`
--

INSERT INTO `category2` (`id`, `subcat`) VALUES
(118, 'Render - Acrylic/Base'),
(133, 'Render - Patching/Prep'),
(134, 'Render - Additives'),
(135, 'Finishes - Texture - Wet'),
(136, 'Finishes - Texture - Dry'),
(137, 'Finishes -  Lime Base'),
(138, 'Finishes - Paints & Membranes'),
(139, 'Tools - Straight Edge'),
(140, 'Tools - Hawks'),
(141, 'Tools - Floats'),
(142, 'Tools - Trowels'),
(149, 'Tools - Handsaws & Knives'),
(148, 'Tools - Ladders Etc'),
(147, 'Tools - Brushes & Rollers'),
(150, 'Accessories - Adhesives'),
(151, 'Accessories - Mesh Joint Tape'),
(152, 'Accessories - Sealants'),
(153, 'Accessories -  Tapes'),
(154, 'Accessories - Caulking Gun'),
(155, 'Accessories - Protective Films'),
(156, 'Beads & Angles - Aluminium'),
(157, 'Beads & Angles - Plastic'),
(158, 'Beads & Angles - Stainless Beads'),
(159, 'Beads & Angles - Starter Beads'),
(160, 'Beads & Angles - Stopping Angles'),
(161, 'Wall Cladding - Styrene'),
(162, 'Wall Cladding - Pre-meshed Styrene'),
(163, 'Wall Cladding - Mesh & Joint Tape'),
(164, 'Wall Cladding - Screws & Washers'),
(165, 'Wall Cladding - Wall Cladding Accessories'),
(166, 'Wall Cladding - Fire Rated Lightweight'),
(167, 'Wall Cladding - LiteW8 AAC Panels'),
(168, 'Mouldings - Architrave Styrene'),
(169, 'Mouldings - Architrave Solids'),
(171, 'Mouldings - Quoins'),
(172, 'Mouldings - Columns'),
(174, 'Specials'),
(175, 'Home Page');

Select allOpen in new window