SQL Subqueries
Subject:   using Left join and MAX and group by function getting wrong reslts
Date:   2007-04-20 23:18:20
From:   satyac46
I have a query like this

SELECT can.iCandidateId AS canId, can.iCInterviewId, concat( can.vFirstName, ' ', can.vMiddleName, ' ', can.vLastName ) AS candidatename, can.vCandidateCode, can.vPost AS designation, cei.vDegree, MAX( cei.vPassoutMonthYear )
FROM candidate AS can
LEFT JOIN `candidate_education_info` AS cei ON can.iCandidateId = cei.iCandidateId
WHERE cei.iCandidateId
IN (

SELECT iCandidateId
FROM candidate AS can
WHERE can.dEntryDate >= '2007-04-15 00:00:00'
AND can.dEntryDate <= '2007-04-21 23:59:59'
GROUP BY can.iCandidateId

i am getting Maximum date but what happend is my degree is not comming to that related date i unable to find where that query is mistaken