Search code examples
mysqlinner-joincreate-view

How to inner join 3 tables?

I'm having trouble with creating a view using the Inner Join, i have these 3 tables

  • Projects (ProjID, ProjName, InitDate, EndDate)
  • Employees (EmpID, EmpName, Phone, City, Salary)
  • Assign (ProjID, EmpID, Hours, Status, Date)

What i am triying to do is to create a view with the name "View1" to show me these information:

  • (ProjID / ProjName / EmpName / Hours) only with the employees who has a Project ID (ProjID) that begins with "N".

I used this query:

CREATE OR REPLACE VIEW view1 AS

SELECT
    projects.ProjID,
    projects.ProjName,
    employees.EmpName,
    assign.Hours
FROM
    assign
    INNER JOIN employees ON employees.EmpID = assign.EmpID
    INNER JOIN projects
WHERE
    projects.ProjID LIKE "N%";

The result i got is not working and it shows multiple times the same employee with different Projects ID

Solution

  • you could use this query

    select * 
from employees
inner join assign on employees.EmpID=assign.EmpID
inner join projects on projects.ProjID=assign.ProjID
where projects.ProjID LIKE "N%";

    the difference with your query is that you need to establish the relation between projects and assignments.

    Anyway if you have the same employee assigned to more than one project that match the where clause, you will get that employee more than once. Maybe you should group and use some aggregation formula like sum(hours)