CODEWITHSUNDEEP
mysql
user444501
user444501

Reputation: 1

MySQL query - multiple having statements not working

I'm trying to use the following query, and if it only has one having statement it works as expected. If I add the second having statement it does not work.

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.

Upvotes: 0

Views: 3100

Answers (2)

Jeremy
Jeremy

Reputation: 122

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.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171589

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

Upvotes: 0

Related Questions