Wrote this the other day, anyone suggestion any improvements? or thoughts?
If it appears a bit odd, the query is created from php depending on certain search values. This query creation is one of the larger and most complex ones
Code: Select all
SELECT
SQL_CALC_FOUND_ROWS
requirement.require_id, requirement.title, requirement.min_ft, requirement.max_ft, requirement.min_m, requirement.max_m,
requirement.min_a, requirement.max_a, requirement.min_h, requirement.max_h, requirement.pdf, requirement.date, requirement.description,
CONCAT(requirement.min_ft, '-', requirement.max_ft, ' sqft, ', requirement.min_m, '-', requirement.max_m, ' meters, ', requirement.min_a, '-', requirement.max_a, ' acres, ', requirement.min_h, '-', requirement.max_h, ' hectares') AS sizeRange
, retailer.account_type, retailer.name AS rName, retailer.retailer_id, retailer.logo
, data_planning_type.planning_id, data_planning_type.class, data_planning_type.type,
GROUP_CONCAT(DISTINCT data_planning_type.class SEPARATOR ', ') AS planType
, GROUP_CONCAT(DISTINCT require_region_inc.region_id SEPARATOR ', ') AS location
, (CASE WHEN ((require_region_exc.region_id = '24517' AND require_region_exc.depth = '3')
OR (require_region_exc.region_id = data_regions_uk.id AND data_regions_uk.county = 'Surrey' AND require_region_exc.depth = '2')
OR (require_region_exc.region_id = data_regions_uk.id AND data_regions_uk.region = 'South East' AND require_region_exc.depth = '1')
) THEN -1 ELSE 0 END) AS notLocation
, users.firstname, users.lastname, users.company
FROM requirement
JOIN retailer ON requirement.retailer_id = retailer.retailer_id
LEFT JOIN require_planning ON requirement.require_id = require_planning.require_id
LEFT JOIN data_planning_type ON require_planning.planning_id = data_planning_type.planning_id
JOIN require_region_inc ON requirement.require_id = require_region_inc.require_id
LEFT JOIN require_region_exc ON requirement.require_id = require_region_exc.require_id
JOIN data_regions_uk ON require_region_inc.region_id = data_regions_uk.id
JOIN user_requirement ON requirement.require_id = user_requirement.require_id
JOIN users ON user_requirement.user_id = users.user_id
WHERE (data_planning_type.planning_id = '1')
AND (require_region_inc.region_id = '24517' AND require_region_inc.depth = 3)
AND (requirement.description LIKE '%a%' OR requirement.title LIKE '%a%')
AND (requirement.min_ft < 1000 AND requirement.max_ft > 1000)
AND (users.firstname LIKE '%a%' OR users.lastname LIKE '%a%')
AND (users.company LIKE '%a%')
AND (retailer.name LIKE '%a%')
GROUP BY
requirement.require_id
HAVING notLocation = 0
ORDER BY retailer.account_type ASC, requirement.date DESC
~pickle | Please use [ code=html ], [ code=php ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: