CODEWITHSUNDEEP
mysqlsql
BentCoder
BentCoder

Reputation: 12740

HAVING clause with MAX function returns no data although there is data

If there are many records for the student, query below works fine but if the student has only one record then query won't return anything unless I remove HAVING clause. Any reason why or any solution?

Thanks

Note: What I'm trying to do is to return the latest visa and passport details for the given student but the problem is, I cannot use ORDER BY because the data in visa and passport are not ordered. Latest record doesn't mean that it is really the latest!

SELECT
    person._______......,
    student._______......,
    passport.passport_expiry_date,
    visa.visa_expiry_date,
FROM person

INNER JOIN student ON student.personid = person.id
LEFT JOIN visa ON visa.personid = person.id
LEFT JOIN passport ON passport.personid = person.id

WHERE
    student.id = '776'

HAVING
    MAX(visa.visa_expiry_date) AND
    MAX(passport.passport_expiry_date)

LIMIT 1

Upvotes: 1

Views: 52

Answers (1)

har07
har07

Reputation: 89285

Try to change this part : 

WHERE
    student.id = '776'

HAVING
    MAX(visa.visa_expiry_date) AND
    MAX(passport.passport_expiry_date)

to this :

WHERE
    student.id = '776'
    AND visa.visa_expiry_date = (SELECT MAX(visa_expiry_date) from visa WHERE visa.personid = student.personid GROUP BY visa.personid)
    AND passport.passport_expiry_date = SELECT MAX(passport_expiry_date) from passport WHERE passport.personid = student.personid GROUP BY passport.personid)

Upvotes: 1

Related Questions