mysql 5.0 is so much nicer than 4.1. i designed these queries on 5.0 using the "JOIN `table` USING ( `field` )" syntax. guess what! mysql 4.1 needs you to hold its hand and be explicit with "ON" statements.
now look how beastly these are:
now look how beastly these are:
-- ------------------------------------------------------------
-- COUNT the number of features for each product
-- ------------------------------------------------------------
SELECT COUNT( sa_product_feature.`feature_id` ) AS `features`,
sa_product.`product_id`,
sa_product.`name` AS `product_name`,
sa_product.`desc_short`,
sa_product.`client`,
sa_product.`server`,
sa_product.`database`,
sa_product.`codebase`,
sa_product.`hosted`,
sa_vendor.`name` AS `vendor_name`
FROM sa_product_feature
JOIN sa_feature
ON sa_product_feature.`feature_id`=sa_feature.`feature_id`
JOIN sa_module
ON sa_feature.`module_id`=sa_module.`module_id`
JOIN sa_industry
ON sa_module.`industry_id`=sa_industry.`industry_id`
JOIN sa_product
ON sa_product_feature.`product_id`=sa_product.`product_id`
JOIN sa_vendor
ON sa_product.`vendor_id`=sa_vendor.`vendor_id`
LEFT JOIN sa_product_segment
ON sa_product.`product_id`=sa_product_segment.`product_id`
LEFT JOIN sa_product_size
ON sa_product.`product_id`=sa_product_size.`product_id`
WHERE sa_industry.`industry_id`='$industry_id'
&& sa_industry.`status`='active'
&& sa_product. `status`='active'
&& sa_vendor. `status`='active'
&& sa_module. `status`='active'
&& sa_feature. `status`='active'
$sql_search
GROUP BY sa_product.`product_id`
ORDER BY features DESC, sa_product.`name`
-- ------------------------------------------------------------
-- ------------------------------------------------------------
-- LIST all features of the specified modules
-- and all of the products with each feature
-- ------------------------------------------------------------
SELECT sa_feature.`name` AS `feature_name`,
sa_module.`module_id`,
sa_module.`name` AS `module_name`,
GROUP_CONCAT( product_id ) AS products
FROM sa_feature
JOIN sa_module
ON sa_feature.`module_id`=sa_module.`module_id`
JOIN sa_industry
ON sa_module.`industry_id`=sa_industry.`industry_id`
LEFT JOIN sa_product_feature
ON sa_feature.`feature_id`=sa_product_feature.`feature_id`
JOIN sa_product
ON sa_product_feature.`product_id`=sa_product.`product_id`
JOIN sa_vendor
ON sa_product.`vendor_id`=sa_vendor.`vendor_id`
LEFT JOIN sa_product_segment
ON sa_product.`product_id`=sa_product_segment.`product_id`
LEFT JOIN sa_product_size
ON sa_product.`product_id`=sa_product_size.`product_id`
WHERE sa_industry.`industry_id`='$industry_id'
&& sa_industry.`status`='active'
&& sa_product. `status`='active'
&& sa_vendor. `status`='active'
&& sa_module. `status`='active'
&& sa_feature. `status`='active'
$sql_search
GROUP by feature_id
ORDER by sa_module.priority, sa_module.module_id,
sa_feature.priority, sa_feature.feature_id
-- ------------------------------------------------------------
and i hate these errors. it's doesn't matter which field--they are joined!!!
MySQL DBMS error 1052: Column 'feature_id' in group statement is ambiguous
those are some massive queries!