Find product that has a certain parts, but not another
Posted: Sun Sep 11, 2005 7:32 am
What I have basically, is a list of items which go together to make a product. So we have a table for products and another table for components, linked by a common field 'prod_id'. In products, we also have 'owner_id' which holds a user ID.
Each component is defined elsewhere, so when we store the parts for a specific product, we just store the id's which link back to their source.
What I need to do is to get a list of all products belonging to a specific user, which has (for arguments sake) component 233, but does not have component 213.
What I have been doing is along the lines of the following:
But it's not working. I've played with the grouping but I'm really just stumbling around in the dark.
Any assistance here would be greatly appreciated. Cheers
Each component is defined elsewhere, so when we store the parts for a specific product, we just store the id's which link back to their source.
What I need to do is to get a list of all products belonging to a specific user, which has (for arguments sake) component 233, but does not have component 213.
What I have been doing is along the lines of the following:
Code: Select all
SELECT `tbl_products`.`prod_id`, `tbl_products`.`product_name` FROM `tbl_products` INNER JOIN `tbl_product_parts` ON (`tbl_products`.`prod_id` = `tbl_product_parts`.`prod_id`) WHERE (`tbl_products`.`owner_id` = '8') AND ((`tbl_product_parts`.`VI_ID` = '233') AND (`tbl_product_parts`.`VI_ID` <> '213'))Any assistance here would be greatly appreciated. Cheers