Page 1 of 1

Find product that has a certain parts, but not another

Posted: Sun Sep 11, 2005 7:32 am
by Stryks
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:

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'))
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

Posted: Sun Sep 11, 2005 8:02 am
by feyd
you may get better results using those selection constraints in the join control. Using the group controls, you'd need to move those constraints to the HAVING clause.