I need to find the names of players from soccer team A that have the same first name but different surname as players from soccer team B. I need to produce the full names of all players from Team A, not just 'John'. I am able to use an additional column with Player IDs unique across all teams.
Basically table looks like this:
FirstName LastName Team
John Snow A
Batman Dies B
John Dies B
I can produce a list of Team A names with the same first name as Team B players.
SELECT firstname, lastname FROM table
Even though surname can't be used for one entry, it should be considered for other entries.
Try the following:
select distinct t1.FirstName
from
(
SELECT firstname, lastname FROM @t where Team = 'A'
) as t1
inner join
(
SELECT firstname, lastname FROM @t where Team = 'B'
) as t2
on (t1.FirstName = t2.FirstName)
where t1.LastName <> t2.LastName