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.