How to join these tables together to get this result

Question

I have attached an ERD of my database. I believe I have all of it set up correctly in Microsoft sql server, but now I am struggling to get my desired result from a query. I will be attaching this database to an application and I want to join the tables together so it is presented nicely in the application.

this is close to what I want, however there are always two people working on a project. Example: Both Kaleb and Laura are working on project P100, but it is only showing one person for each project.

If more information/pictures is needed please let me know

Answer 1

Based on your ERD, you should be using the "Assignments" table as the main/primary table in your query and joining from there to the other tables to get the information you're after. e.g.

SELECT sfa.Project_ID, sfp.Project_Type, sfp.Project_Status, sfe.EMP_FNAME, sfj.JOB_TITLE
FROM sfAssignments AS sfa
INNER JOIN sfProjects AS sfp ON sfa.Project_ID = sfp.Project_ID
INNER JOIN sfEmployees AS sfe ON sfa.EMP_ID = sfe.EMP_ID
INNER JOIN sfJobs AS sfj ON sfe.JOB_CODE = sfj.JOB_CODE