Help with myql query - multiple having statements not working

Question

I'm trying to use the following query, if it only has one having statement it works as expected, however if i add the second having statement it does not work. Could someone please give me some assistance....

SELECT candidate.first_name, 
 candidate.last_name, 
 qualification.code, 
 property.value AS funding_band_value, 
 qualification.funding_band,
 property.value AS qualification_level_value,
 qualification.qualification_level_id
FROM candidate_qualification, candidate, qualification, property
WHERE candidate_qualification.candidate_id=candidate.id and
candidate_qualification.qualification_id=qualification.id
HAVING funding_band_value = (select property.value from property where qualification.funding_band=property.id) and
HAVING qualification_level_value = (select property.value from property where qualification.qualification_level_id=property.id)

Could someone explain why this doesn't work and how I should do this.

Many thanks for your help.

Answer 1

You don't need HAVING here, just use AND so it is part of your WHERE clause.

The subqueries are not necessary, those tables are already joined.

Something like this should be closer to what you want:

SELECT c.first_name, 
    c.last_name, 
    q.code, 
    p.value AS funding_band_value, 
    q.funding_band,
    p.value AS qualification_level_value,
    q.qualification_level_id
FROM candidate_qualification cq 
INNER JOIN candidate c ON cq.candidate_id=c.id 
INNER JOIN qualification q ON cq.qualification_id=q.id
INNER JOIN property p ON q.funding_band=p.id
    and q.qualification_level_id=p.id

Answer 2

HAVING acts similarly to WHERE or GROUP BY. You reference it once to start using it and combine multiple statements with AND or OR operators. An in depth look at the query parser might give you a more explicit answer.