The query below is correct. It returns the rows `paul` and `rick` because they have the highest rating of the child rows. This was solved for me with the below link. I am having trouble understanding the query. I understand joins, what i don't understand is `t2.rating is null`?
select t1.* from mytable t1 left join mytable t2 on t1.parentid = t2.parentid and t1.rating < t2.rating join mytable parents on parents.id = t1.parentid where t2.rating is null AND parents.name like '%mike%'
`t1.rating < t2.rating` attaches the highest rated values to the LEFT table. I know `t2.rating is null` means t2.rating is false but i have no idea what it is doing in the query? Does that mean it removes from t1 where there isn't a match in the LEFT JOIN query?
my head hurts... the simpler the explanation the better.