Darren O'BRIEN Darren O'BRIEN - 1 year ago 81
MySQL Question

Difficulty selecting data from multiple tables with a sub-query


Below is a relational schema I have created to assist in answering my question.

JOBS (**jobClass**, Description, chargePerHour)

EMPLOYEES (**empNo**, *jobClass*, firstName, lastName)

Foreign Key jobClass references JOBS

PROJECTS (**projectNo**, projectName)

PROJECTS2EMPLOYEES (**projEmpKey**, *projectNo*, *empNo*, hoursBilled, projectLeader)

Foreign Key projectNo references PROJECTS

Foreign Key empNo references EMPLOYEES

Bold = PK (Primary Key)
Italic = FK (Foreign Key)

I am trying to select projectName from the Projects table and the full name of the project leader for each project from the Employees table.

Project leaders are differentiated by a 1 in the projectLeader field in the projects2employees tables.

My last attempt was

SELECT projectName, CONCAT_WS(" ", firstName, lastName) FROM Projects, Employees WHERE projectNo IN
(SELECT projectNo from projects2employees WHERE projectLeader = 1)

but it didn't work out very well.

Any help would be appreciated

Answer Source

Try this :

SELECT p.projectName, CONCAT(firstName, ' ', lastName)
  FROM Projects p
  JOIN projects2employees pe
    ON (p.projectNo = pe.projectNo and pe.projectLeader = 1)
  JOIN employees e
    ON (pe.empno= e.empno)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download