I hope that someone can help me.
I need to search to filter out values in a database. The database has about 14 000 records of Koalas that have come into care.
Each Koala has their own name with a fate that has happened with it.
I need to extract the koalaName for the Koala that has been Relocated, Rescued, or In Care but eliminate the koalaName if it has been eventually Found Dead.
In the case of the output above Alec is eliminated in the results as he has eventually Died, Euthanased or Found Dead. In the case of Aleta and Alex I just need to get their name once.
This is the query I've been trying to work with.
SELECT `id`, `koalaName`, `fate` FROM main A WHERE EXISTS ( SELECT `id`, `koalaName`, `fate` FROM main B WHERE A.koalaName = B.koalaName AND B.`fate` REGEXP 'Released|Relocated|Care' ) AND NOT EXISTS ( SELECT `id`, `koalaName`, `fate` FROM main B WHERE A.koalaName = B.koalaName AND B.`fate` REGEXP 'Died|Euth|Dead' )
I just don't seem to be able to get the result to output the koalaName as a single value instead of returning multiple values
e.g. Alex should only appear once.
Many thanks in advance, I think its simple but I just don't seem to be able to do it.