Someone on here helped put together this extremely useful query!
SELECT * FROM results r
INNER JOIN categories c on r.id = c.result_id
WHERE c.name in ('$purpose', '$prop_type', '$loan_type')
GROUP BY r.id
HAVING COUNT(c.c_id) = 3
LIMIT 50 OFFSET 0
Which is working great! However, when I try to order the results by using
SELECT * FROM results r
INNER JOIN categories c on r.id = c.result_id
WHERE c.name in ('$purpose', '$prop_type', '$loan_type')
ORDER BY r.usefulness DESC
GROUP BY r.id
HAVING COUNT(c.c_id) = 3
LIMIT 50 OFFSET 0
I get a syntax error. Am I missing something obvious here?
Thanks a lot!
there is an order for the parts of the query, and ORDER BY should be in the end (before the LIMIT and the OFFSET... try this:
SELECT * FROM results r
INNER JOIN categories c on r.id = c.result_id
WHERE c.name in ('$purpose', '$prop_type', '$loan_type')
GROUP BY r.id
HAVING COUNT(c.c_id) = 3
ORDER BY usefulness DESC
LIMIT 50 OFFSET 0
also make sure that you have spaces in the end of any row...
notice that in mysql that is a shortened version for LIMIT x OFFSET y - you can just write LIMIT y,x
ORDER BY has to come after the HAVING.