RE: [PHP] MySQL Query Help

SELECT * FROM products p LEFT JOIN criteria_values cv ON p.key=cv.key LEFT JOIN criteria c ON cv.key=c.key WHERE c.value IS NOT NULL Hard to answer without more detail, but I am guessing the answer will be something like the above. Your question makes it hard to understand whether c or cv is joined to p. So swap em around if I misunderstood. iPhone 4. It rocks! On Nov 21, 2010, at 1:37 AM, Simcha Younger <simcha@syounger.com> wrote: > On Sat, 20 Nov 2010 13:54:29 -0700 > "Ben Miller" <biprellim@gmail.com> wrote: > >> 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. > > It would be a lot easier to think about this if you could provide the table structure or create table statements. > > If I understood correctly, you have products which reference a criteria ID which has no matching value. If this is the problem you have a to first take care of the integrity of your data, as this should never happen. > To help clarify - the 3 tables look something like the following (tableName => column,column,column...): Products => product_id,product_name,product_description... (key = product_id) Criteria => criteria_id,criteria_title,criteria_text,... (key = criteria_id) Criteria_values => product_id,criteria_id,criteria_value,... (key = product_id & criteria_id) The user selects up to X product_id's to compare, stored in $selected_products. I then need to get each criteria_title and criteria_text from table(criteria) where there is a matching criteria_id in table(criteria_values) for each/all $selected_products, also returning the criteria_value for each $selected_products, ultimately ending up with an array or object that looks something like: (Assuming the user selected Product A (product_id=1), Product B (product_id=2) and Product C (product_id=3) criteria => Array ( [$criteria_id] => Array ( [title] => query_row[criteria_title] [text] => query_row[criteria_text] [values] => Array ( [1] => Product A's value for this criteria [2] => Product B's value for this criteria [3] => Product C's value for this criteria ) ) [$criteria_id] => Array ( ..... ) ) Again, displaying only/all criteria where there is a matching value for each/all $selected_products Thanks again, Ben