MySQL Query Help

From: Date: Sat, 20 Nov 2010 20:54:29 +0000
Subject: MySQL Query Help
Groups: php.general 
Request: Send a blank email to php-general+get-309589@lists.php.net to get a copy of this message
Hi, I'm building a website for a client in which I need to compare their products, side-by-side, but only include criteria for which all selected products have a value for that criteria. In my database (MySQL), I have a tables named "products","criteria" and "criteria_values" If I have something like $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All products selected for comparison by the user I need to get only rows from "criteria" where there is a row in "criteria_values" matching "criteria.criteria_id" for each $selected_product - in other words, if any of the $selected_product does not have a row in "criteria_values" that matches "criteria.criteria_id", that criteria would not be returned. I hope that makes sense. I've played around with a few join queries, but none have given the desired results. Best I've been able to come up with so far is to query "criteria" for each DISTINCT(criteria_id) and then run through each $selected_product to make sure each has a criteria_value with a matching criteria_id, eliminating any criteria where the number of criteria_values < count($selected_product), but this seems pretty inefficient. Thanks in advance for any help. Ben Miller

« previous php.general (#309589) next »