Search code examples
sql-serversql-order-byinner-join

How do I find the name of soccer players with same first name but different surnames?


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.


Solution

  • 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