Find product that has a certain parts, but not another

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Find product that has a certain parts, but not another

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply