[Taipan]

Originally Posted by Old Pedant

Oh, I see! Very clever!

One comment to start with: The products_extra_categories.id field is completely unnecessary and might even be considered a mistake.

A better way to declare that table would be:

CREATE TABLE IF NOT EXISTS products_extra_categories (
  product_id int NOT NULL,
  category_id int NOT NULL,
  PRIMARY KEY  (product_id, category_id)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

This also prevents you from duplicating any product_id/category_id combinations!

Thanks for the advice on that. I used the id to make deleting it easier, but your way does look better.

And also appreciate the advice on the field names for id and name etc. I will start to use these more unique names in future.

I tried both of your other queries and I am still not getting the full list. Sorry if my explanations are poor.

All I really need to retrieve is a list of product id's. I am just grabbing the category name while I am getting them. So I know the category id that I want the list of products from (3 for this example), getting any products whose products.category_id = 3 is simple. But the products whose values are in the products_extra_categories will have a different category_id so I think the 'WHERE p.category_id = 3' is making it only return products with that category id.

What I need to get is a list of product id's where products.category_id = 3 and include the product_id's of any entries in products_extra_categories where products_extra_categories.category_id = 3.

I am confusing myself just trying to explain it. Maybe I have gone about the whole extra_category approach the wrong way.

I know I can get them using 2 queries, one on products and the other on products_extra_categories but I want the result to be ordered by the product name. Really appreciate the help.