CODEWITHSUNDEEP
sqlsql-serverdistinct
alwaysVBNET
alwaysVBNET

Reputation: 3310

Distinct doesn't work when joining two tables

I have two tables: Employee and DealerSessions which are linked based on the EmployeeID. The following search query does not return the distinct EmployeeID's. Any ideas?

SELECT  distinct e.EmployeeID, e.Name, e.Surname, ds.DealerSessionsID, ds.SessionID, ds.EmployeeID AS Expr1
FROM Employee AS e INNER JOIN
                      DealerSessions AS ds ON e.EmployeeID = ds.EmployeeID
WHERE (ds.SessionID = 154)

Upvotes: 0

Views: 2850

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Correct. distinct returns distinct of all the fields. If you want distinct EmployeeIds, then try something like:

SELECT  distinct e.EmployeeID
FROM Employee AS e INNER JOIN
     DealerSessions AS ds ON e.EmployeeID = ds.EmployeeID
WHERE (ds.SessionID = 154)

The additional fields are resulting in multiple rows. If you want one row, then you can do something like this:

select EmployeeID, Name, Surname, DealerSessionsID, SessionID, Expr1
from (SELECT e.EmployeeID, e.Name, e.Surname, ds.DealerSessionsID, ds.SessionID, ds.EmployeeID AS Expr1,
             row_number() over (partition by e.EmployeeId order by (select NULL)) as seqnum
      FROM Employee AS e INNER JOIN
           DealerSessions AS ds ON e.EmployeeID = ds.EmployeeID
      WHERE (ds.SessionID = 154)
     ) t
where seqnum = 1;

This randomly selects one row. If you want a particular row (like the most recent) then choose another expression for the order by.

Upvotes: 3

John Emeres
John Emeres

Reputation: 473

This is because you are also adding columns from DealerSessions table to the SELECT statement.

You probably have one-to-many relationship between these two which causes to have multiple entries in DealerSessions table for each employee.

You can either remove some columns from SELECT statement or narrow criteria even more using more conditions in WHERE statement.

Upvotes: 0

Related Questions