join_rmd

loading the employees dataset

library(dplyr)

Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

employees<-data.frame(emp_id=c(1,2,3,4,5),emp_name=c("Alexa","Barath","Chandran","Divya","Elango"),gender=c("F","M","M","F","M"),dept_id=c(1,1,2,2,3))
employees

  emp_id emp_name gender dept_id
1      1    Alexa      F       1
2      2   Barath      M       1
3      3 Chandran      M       2
4      4    Divya      F       2
5      5   Elango      M       3

departments<-data.frame(dept_id=c(1,2,3,4),dept_name=c("HR","Finance","IT","Marketing"))
departments

  dept_id dept_name
1       1        HR
2       2   Finance
3       3        IT
4       4 Marketing

projects<-data.frame(proj_id=c(1,2,3,4),proj_name=c("ProjectA","ProjectB","ProjectC","ProjectD"),emp_id=c(1,2,2,NA))
projects

  proj_id proj_name emp_id
1       1  ProjectA      1
2       2  ProjectB      2
3       3  ProjectC      2
4       4  ProjectD     NA

What are the employees who work currently assigned to projects?

d1<-inner_join(employees,projects,by="emp_id")
d1

  emp_id emp_name gender dept_id proj_id proj_name
1      1    Alexa      F       1       1  ProjectA
2      2   Barath      M       1       2  ProjectB
3      2   Barath      M       1       3  ProjectC

What are all employees and their assigned projects,including those who are not assigned to any project?

d2<-left_join(employees,projects,by="emp_id")
d2

  emp_id emp_name gender dept_id proj_id proj_name
1      1    Alexa      F       1       1  ProjectA
2      2   Barath      M       1       2  ProjectB
3      2   Barath      M       1       3  ProjectC
4      3 Chandran      M       2      NA      <NA>
5      4    Divya      F       2      NA      <NA>
6      5   Elango      M       3      NA      <NA>

What are all projects and the employees assigned to them including project that assigned employee?

d3<-left_join(projects,employees,by="emp_id")
d3

  proj_id proj_name emp_id emp_name gender dept_id
1       1  ProjectA      1    Alexa      F       1
2       2  ProjectB      2   Barath      M       1
3       3  ProjectC      2   Barath      M       1
4       4  ProjectD     NA     <NA>   <NA>      NA

What is the complete list of employees and project including those without assignments on either side?

d4<-full_join(employees,projects,by="emp_id")
d4

  emp_id emp_name gender dept_id proj_id proj_name
1      1    Alexa      F       1       1  ProjectA
2      2   Barath      M       1       2  ProjectB
3      2   Barath      M       1       3  ProjectC
4      3 Chandran      M       2      NA      <NA>
5      4    Divya      F       2      NA      <NA>
6      5   Elango      M       3      NA      <NA>
7     NA     <NA>   <NA>      NA       4  ProjectD

Which employees are assignrd to at least one project?List them without repeating project details.

d5<-semi_join(employees,projects,by="emp_id")
d5

  emp_id emp_name gender dept_id
1      1    Alexa      F       1
2      2   Barath      M       1

Which employees are not assigned to any project?List those employees.

d6<-anti_join(employees,projects,by="emp_id")
d6

  emp_id emp_name gender dept_id
1      3 Chandran      M       2
2      4    Divya      F       2
3      5   Elango      M       3