[Taipan]

Thanks for your help.

I still can't get it to work using the INNER JOIN and LEFT JOIN. To make it a bit clearer my tables are set up like this, I trimmed excess fields out.

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(16) NOT NULL auto_increment,
  `category_id` mediumint(9) default NULL,
  `name` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

CREATE TABLE IF NOT EXISTS `product_categories` (
  `id` smallint(6) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=98 ;

CREATE TABLE IF NOT EXISTS `products_extra_categories` (
  `id` int(16) NOT NULL auto_increment,
  `product_id` int(16) NOT NULL,
  `category_id` int(16) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

So a product is linked to it's primary category as products.category_id = product_categories.id and if there are any extra categories then products_extra_categories.product_id = products.id and products_extra_categories.category_id = product_categories.id. A product can have multiple extra categories.

With the query, I specify the category id, using 3 in this example. At the moment I have the query as:-

SELECT 
    p.id, 
    c.name 
FROM 
    products AS p 
    INNER JOIN product_categories AS c ON p.category_id = 3 AND p.category_id = c.id 
    LEFT JOIN products_extra_categories AS e ON p.id = e.product_id AND c.id = e.category_id 
ORDER BY p.name

It is still only returning the product with category_id = 3 and not returning the products_extra_categories entry which has category_id 3 and product_id 7.

I hope this explains it better.