CODEWITHSUNDEEP
sqlsql-server
Prachi Pant
Prachi Pant

Reputation: 43

sql query for many to one relationship

I have a table with 3 columns

table: StaffDepartmentAssgnment

StaffId         DepartmentId          AssignedFromDate

S1              Dept1                 2013-02-08
S2              Dept1                 2013-02-08
S3              Dept2                 2013-02-01 
S1              Dept2                 2013-02-01

I want to find out all the StaffIds which are currently in Dept2.How do i write a query for it?

Upvotes: 3

Views: 80

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460038

Perhaps:

SELECT DISTINCT sd.StaffId         
FROM StaffDepartmentAssgnment sd
WHERE sd.DepartmentId = 'Dept2'

Update

S1 is not in Dept2 now,he has been shifted to Dept1..i need to pick up the top AssignedFromDate

Since you're using SQL-Server you can use a CTE with the ROW_NUMBER function:

WITH CTE AS (
    SELECT sd.staffid, sd.DepartmentId,
           rn = Row_number() OVER ( 
                    Partition BY sd.staffid 
                    ORDER BY assignedfromdate DESC) 
    FROM   staffdepartmentassgnment sd) 
SELECT staffid 
FROM   cte 
WHERE  rn = 1 
AND    DepartmentId = 'Dept2'

Demo

Upvotes: 2

juergen d
juergen d

Reputation: 204746

This is a DB engine independent solution

select * from 
( 
   select StaffId, max(AssignedFromDate) as adate
   from StaffDepartmentAssgnment 
   group by staffid
) x
inner join StaffDepartmentAssgnment y
      on y.staffid = x.staffid and adate = y.AssignedFromDate
where DepartmentId = 'dept2'

SQLFiddle demo

Upvotes: 3

Related Questions