Hey, I don't think this is possible (as I think the WHERE section of an SQL query applies before your results are grouped but.... is there any way to use MAX or COUNT in the WHERE statement?
It's not an ideal situation but we've got a database that imported records from a few separate databases and so have some duplication of data (well, not all of it is duplicated, but there's an overlap on name and surname hence why I am using this to group the items together).
The above basically says, if one of the person records of a certain name is flagged as 'deceased' (ie deaded will equal 1) then disregard all records of that name. What I'd like to be able to do is add a condition that's basically WHERE deaded != 1 [ or MAX(pd.status = 'deceased') != 1 ]. Is that possible at all?Code:SELECT p.name, p.surname, concat(`name`,' ',`surname`) as fullname, MAX(pd.status = 'deceased') as deaded FROM person p LEFT JOIN person_demographics pd on pd.person_id = p.person_id WHERE pd.contact_approved = 1 GROUP BY concat(`name`,' ',`surname`) ORDER BY deaded asc, fullname asc


LinkBack URL
About LinkBacks
Reply With Quote

