jaxdevil Posted August 18, 2012 Share Posted August 18, 2012 Ok, I am trying to find all the results in a table that match specific variables (obviously) but there is a table that contains both ink types and tip types, and I am trying to locate those that match the ink type and color types selected. I have tried numerous queries with the same zero result, even though I know those brands have those ink and color types available. Here is my query: SELECT DISTINCT(`pc`.`pid`) AS `dpid`, `c`.`category_path` AS `category_path`, `c`.`parent` AS `cid` FROM `catalog` AS `c`, `products` AS `p`, `products_categories` AS `pc`, `brand_names` AS `bn`, `products_attributes` AS `pa`, `products` AS `p2`, `products_attributes` AS `pa`, `products_attributes` AS `pa2` WHERE `c`.`cid`=`pc`.`cid` AND `p`.`pid`=`pc`.`pid` AND `pc`.`bid`=`bn`.`id` AND `bn`.`brand_name`='begreen' AND `c`.`level`='3' AND `c`.`url_default` LIKE 'ca%' AND `p`.`pid`=`pa`.`pid` AND `p2`.`pid`=`pa2`.`pid` AND (`pa`.`options_con` LIKE '%|Green|%') AND (`pa2`.`options_con` LIKE '%|0.7mm|%') ORDER BY `c`.`category_path` ASC Any help will be greatly appreciated. Quote Link to comment Share on other sites More sharing options...
jaxdevil Posted August 18, 2012 Author Share Posted August 18, 2012 Tried revising it with this: SELECT`pc`.`pid` AS `dpid`, `c`.`category_path` AS `category_path`, `c`.`parent` AS `cid`, `pa`.`options_con` AS `pa_options_con`, `pa2`.`options_con` AS `pa2_options_con` FROM `catalog` AS `c`, `products` AS `p`, `products` AS `p2`, `products_categories` AS `pc`, `brand_names` AS `bn`, `products_attributes` AS `pa`, `products_attributes` AS `pa2` WHERE `c`.`cid`=`pc`.`cid` AND `p`.`pid`=`pc`.`pid` AND `pc`.`bid`=`bn`.`id` AND `bn`.`brand_name`='begreen' AND `c`.`level`='3' AND `c`.`url_default` LIKE 'ca%' AND `p`.`pid`=`pa`.`pid` AND `p2`.`pid`=`pa2`.`pid` AND `p`.`pid`=`p2`.`pid` AND (`pa`.`name`='Ink Type' AND (`pa`.`options_con` LIKE '%|Green|%')) AND (`pa2`.`name`='Point Type' AND (`pa2`.`options_con` LIKE '%|0.7mm|%')) ORDER BY `c`.`category_path` ASC Since the products_attributes table contains both the pid (product ID) a name (the name of option type) and also the different options concatenated with a pipe "|" to separate them for searches. So i figured limiting those two to looking for the ones that are of the Ink Color or Tip Type that match the selected options the user sent as variables it would work, NOT. I'm suckin today. Someone with a big brain lend a lesser mind a hand Quote Link to comment Share on other sites More sharing options...
jaxdevil Posted August 18, 2012 Author Share Posted August 18, 2012 Never mind... got it... SELECT DISTINCT(`pc`.`pid`) AS `dpid`, `c`.`category_path` AS `category_path`, `c`.`parent` AS `cid`, `pa`.`name` AS `pa_name`, `pa`.`options_con` AS `pa_options_con`, `pa2`.`name` AS `pa2_name`, `pa2`.`options_con` AS `pa2_options_con` FROM `catalog` AS `c`, `products` AS `p`, `products` AS `p2`, `products_categories` AS `pc`, `brand_names` AS `bn`, `products_attributes` AS `pa`, `products_attributes` AS `pa2` WHERE `c`.`cid`=`pc`.`cid` AND `p`.`pid`=`pc`.`pid` AND `pc`.`bid`=`bn`.`id` AND `bn`.`brand_name`='begreen' AND `c`.`level`='3' AND `c`.`url_default` LIKE 'ca%' AND `p`.`pid`=`pa`.`pid` AND `pa2`.`pid`=`p`.`pid` AND (`pa`.`name`='Ink Type' AND ((`pa`.`options_con` LIKE '%|Other|%')) OR (`pa2`.`name`='Point Type' AND (`pa2`.`options_con` LIKE '%|0.7mm|%'))) ORDER BY `c`.`category_path` ASC That was a pain.. grrrr Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.