SQLTeam.com | Weblogs | Forums

How to select Distinct row

I need to select all supervisor records. Our HR employee table set up like below

Firstname | Lastname | Email | Supervisor

Frank | Johns | fjohns | Taylor, Don
Pat | Hope | phope | Taylor, Don
Jen | Dow | jdow | Taylor, Don
Taylor | Don | tdon | Olson, Mike
Kim | Ronda | kronda | Olson, Mike
Rob | Smith | rsmith | Olson, Mike
Mike | Olson | molson | null

The final result should be like this

Firstname | Lastname | Email | Supervisor

Taylor | Don | tdon | Olson, Mike
Mike | Olson | molson | null

If I use Distinct on supervisor then it gives me a list of supervisor but not their info. Please help

SELECT Firstname, Lastname, Email, Supervisor
FROM MyTable
WHERE Lastname + ', ' + Firstname IN (
    SELECT Supervisor
    FROM MyTable
    WHERE Supervisor IS NOT NULL
);

I figured it out. Thanks,

Perhaps you can take a look at the SQL statement EXISTS.

SQL Server IN vs EXISTS (mssqltips.com)

if you do a self join to the same table

TableA
Name Supervisor
aa 123
123 bb
bb null

select  
        xx.Name  -- supervisors Name 
    from 
          TableA    xx
                  join 
           TableA  yy
                 on xx.Name = yy.Supervisor