[sql] SQL Server - INNER JOIN WITH DISTINCT

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.

It's not the same doing a select distinct at the beginning because you are wasting all the calculated rows from the result.

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

try that.

add "distinct" after "select".

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

You can use CTE to get the distinct values of the second table, and then join that with the first table. You also need to get the distinct values based on LastName column. You do this with a Row_Number() partitioned by the LastName, and sorted by the FirstName.

Here's the code

;WITH SecondTableWithDistinctLastName AS
(
        SELECT  *
        FROM    (
                    SELECT  *,
                            ROW_NUMBER() OVER (PARTITION BY LastName ORDER BY FirstName) AS [Rank]
                    FROM    AddTbl
                )   
        AS      tableWithRank
        WHERE   tableWithRank.[Rank] = 1
) 
SELECT          a.FirstName, a.LastName, S.District
FROM            SecondTableWithDistinctLastName AS S
INNER JOIN      AddTbl AS a
    ON          a.LastName = S.LastName
ORDER   BY      a.FirstName

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

hope this helps

I think you actually provided a good start for the correct answer right in your question (you just need the correct syntax). I had this exact same problem, and putting DISTINCT in a sub-query was indeed less costly than what other answers here have proposed.

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