SQL Server - INNER JOIN WITH DISTINCT

Question

I am having a hard time doing the following:

select a.FirstName, a.LastName, v.District
from AddTbl a order by Firstname
inner join (select distinct LastName from
            ValTbl v  where a.LastName = v.LastName)  

I want to do a join on ValTbl but only for distinct values.

Answer 1

UPDATE (2023): Denis M. Kitchen's answer can be better than my answer from a performance point of view, but it's important to mention that his query can produce a different result than mine, if the same (FirstName, LastName) combination appears in more than 1 record in AddTbl (see the example here). This can be either good or bad, depending on the particular use case.

---

MY ORIGINAL ANSWER (2011):

Try this:

select distinct a.FirstName, a.LastName, v.District
from AddTbl a 
  inner join ValTbl v
  on a.LastName = v.LastName
order by a.FirstName;

Or this (it does the same, but the syntax is different):

select distinct a.FirstName, a.LastName, v.District
from AddTbl a, ValTbl v
where a.LastName = v.LastName
order by a.FirstName;