Question 272864 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL join with pivot

I have a couple of tables i want to join. They are connected through a pivot table.

I have a projects table, a employees table and a pivot table called employee_project that has both a project_id and a employee_id:

id employee_id project_id

I want to get the first_name and last_name from the employees table but the where clause should search in the company_id and course_id on the projects table.

So far i have:

SELECT * 
FROM projects
JOIN employees
     ON employees.id = employee_project.employee_id
JOIN employee_project 
     ON projects.id = employee_project.project_id 
WHERE course_id = 3 AND company_id = 150004

But this obviously isn’t working as expected.

Tags:

2

Answers


  1. 0

    The issue in your query is that you’re using employee_project table before joining it. I just changed the order of join and it seems working!

    SELECT
    employees.first_name,
    employees.last_name 
FROM
    projects
    JOIN employee_project ON projects.id = employee_project.project_id
    JOIN employees ON employees.id = employee_project.employee_id 
WHERE
    projects.course_id = 3 
    AND projects.company_id = 150004;

    This should give you the first_name and last_name of employees who are associated with projects that meet your criteria.

    Login or Signup to reply.
  2. 0

    you should join employee_project table first, and then join employees table

    SELECT * 
FROM projects 
JOIN employee_project  ON projects.id = employee_project.project_id  
JOIN employees ON employees.id = employee_project.employee_id 
WHERE course_id = 3 AND company_id = 150004 ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search