How to select distinct value

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

1 answer

  • answered 2022-05-06 18:28 squillman

    This should work (I assume the transposed First / Last for Don Taylor is a typo).

    SELECT DISTINCT d.Firstname, d.Lastname, d.Email, d.Supervisor
    FROM MyTable d
    INNER JOIN MyTable sup ON sup.Supervisor = d.Lastname + ', ' + d.Firstname;
    

    or

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

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum