CODEWITHSUNDEEP
sqlsql-serverjoin
Shomaail
Shomaail

Reputation: 493

to get unique records from table after join

I hav tables event, session, sessionspeaker, speaker and Employee event table has EventID as PK, session table can have multiple Eventid and has sessionid as pk, speaker table has speakerid as pk and has empID column, employee table has empID as PK. sessionspeaker table has (sessionid, speakerid) as pk. Now I wud like to hav all the eventid with Topmost emplID such that the event id is unique 

SELECT     emp.EmployeeID, e.EventID
FROM    Event AS e INNER JOIN Session ON e.EventID = Session.EventID left outer JOIN
SessionSpeaker ON Session.SessionID = SessionSpeaker.SessionID INNER JOIN
Speaker ON SessionSpeaker.EmployeeID = Speaker.EmployeeID INNER JOIN
Employee AS emp ON Speaker.EmployeeID = emp.EmployeeID

this query returns 

EmployeeID   EventID
5351         310
5351         310
5352         299
5352         300
5352         301
5352         302
5352             303
5352         314
5352         315

this is what I don't want, I want unique event ID and topmost employeeID Kindly, let me know an easy way to solve this. will groupby, having or count help in this matter

Upvotes: 1

Views: 119

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

I want unique event ID and topmost employeeID

Just GROUP BY e.EventID and select MAX(EmployeeId):

SELECT 
  e.EventID, 
  MAX(emp.EmployeeID) AS TopMostEmpId, 
FROM    Event AS e 
INNER JOIN Session         ON e.EventID                 = Session.EventID 
INNER JOIN SessionSpeaker  ON Session.SessionID         = SessionSpeaker.SessionID 
INNER JOIN Speaker         ON SessionSpeaker.EmployeeID = Speaker.EmployeeID 
INNER JOIN Employee AS emp ON Speaker.EmployeeID        = emp.EmployeeID
GROUP BY e.EventID

If you want to select more columns than eventId, employeeId you can use a ranking function something like this:

WITH CTE AS
(
    SELECT 
      e.EventID, 
      emp.EmployeeID,
      e.EventName,
      emp.EmployeeName,
      ...
      ROW_NUMBER() OVER(PARTITION BY e.EventID 
                        ORDER BY emp.EmployeeId DESC) AS RN
    FROM    Event AS e 
    INNER JOIN Session         ON e.EventID                 = Session.EventID 
    INNER JOIN SessionSpeaker  ON Session.SessionID         = SessionSpeaker.SessionID 
    INNER JOIN Speaker         ON SessionSpeaker.EmployeeID = Speaker.EmployeeID 
    INNER JOIN Employee AS emp ON Speaker.EmployeeID        = emp.EmployeeID
) 
SELECT *
FROM CTE
WHERE RN = 1;

Upvotes: 3

apomene
apomene

Reputation: 14389

SELECT     emp.EmployeeID, distinct e.EventID
FROM    Event AS e INNER JOIN Session ON e.EventID = Session.EventID left outer JOIN
SessionSpeaker ON Session.SessionID = SessionSpeaker.SessionID INNER JOIN
Speaker ON SessionSpeaker.EmployeeID = Speaker.EmployeeID INNER JOIN
Employee AS emp ON Speaker.EmployeeID = emp.EmployeeID 

group by e.EventID

Upvotes: 0

Related Questions