MySQL query troubles

Question

I have this query here:

select distinct oawa_id, 'Team Impact' as Club, FirstName, LastName
from team_impacts ti
left join session_participants sp
    on (sp.member_id = ti.id)
    or (sp.member_id = ti.oawa_id)
order by LastName asc, FirstName asc

and this provides me with a list of users, but some users have the same first name and last name and some come with an oawa_id and some don't. For example:

This is record list:

1. Brad May has an oawa_id
2. Brad May doesnt have an oawa_id

it's okay if some people do not have an oawa_id, but for cases like these, if people have the same name, get the one with the oawa_id and ignore the one without. That only applies to people with the same name.

Any suggestions?

Answer 1

All you need to do is restructure your query with a group by clause

select max(oawa_id) as oid, 'Team Impact' as Club, FirstName, LastName
                                    from team_impacts ti
                            left join session_participants sp
                                    on (sp.member_id = ti.id)
                                    or (sp.member_id = ti.oawa_id)
                            group by Club,FirstName, LastName
                            order by LastName asc, FirstName asc