LEFT JOIN to a subquery

[emedal63]

Hi, I'm trying to left join an employee table to a subqueried-trainings table.

 

Employees Table:

$query= "SELECT * FROM employees WHERE employees.department='Oncology'";

ID -- NAME -- DEPARTMENT

123 -- Joe -- Oncology

456 -- Mary -- Oncology

789 -- Alex -- Oncology

 

Trainings Table:

$query = "SELECT trainings.id, GROUP_CONCAT(subject) FROM trainings GROUP BY trainings.cid";

This query is necessary to get one row for each ID and the training SUBJECT concated in the following column

ID -- SUBJECT

001 -- apple, orange

123 -- apple, orange, pear

789 -- orange, pear

 

How do I write a LEFT JOIN so that I get all the people from employees table, then add the SUBJECT column to  the matching ID? The result should look like this:

ID -- NAME -- DEPARTMENT -- SUBJECT

123 -- Joe -- Oncology -- apple, orange, pear

789 -- Alex -- Oncology -- orange, pear

 

Many thanks!!!

[waynewex]

 

 

 

SELECT * FROM employees

LEFT JOIN trainings ON employees.ID = trainings.ID

[emedal63]

Thanks Waynewex. Your suggestion works but it will give me multiple rows with the same ID. I need the result to have one ID per row, thus the reason for trying to use the subquery with GROUP_CONCAT.

[mikosiko]

 

try:

SELECT a.*, GROUP_CONCAT(b.subject)
  FROM employees AS a
      LEFT JOIN trainings AS b ON a.id = b.id
                  AND a.department = 'Oncology'
GROUP BY a.id;

[emedal63]

Thanks mikosiko, your suggestion kind of works. It's giving me a list of all the employees (not just Oncology) and it applies the GROUP_CONCAT to just the Oncology employees. The result looks like:

ID -- NAME -- DEPARTMENT

012 -- Mary -- Surgery

123 -- Joe -- Oncology -- apple, orange, pear

127 -- Rich -- Finance

789 -- Alex -- Oncology -- orange, pear

 

Can the query be modiefied so that the result shows ONLY Oncology employees AND it applies the GROUP_CONCAT to those employees?

[mikosiko]

replace LEFT JOIN with JOIN

[emedal63]

Thanks mikosiko. It looks like using just JOIN gives me an inner join where it will only give me ID's that exist on both the emloyees table and trainings table. It's the best solution so far. However, if an employee has NOT done a training, the name won't show up in the result.

 

I think LEFT JOIN is what I need, but I can't get it to play nicely with GROUP_CONCAT. Any other ideas?

 

Thanks again for all your help!

[mikosiko]

it should work in that case

SELECT a.*, GROUP_CONCAT(b.subject)
  FROM employees AS a
      LEFT JOIN trainings AS b ON a.id = b.id
  WHERE a.department = 'Oncology'
GROUP BY a.id;

[emedal63]

mikosiko, you are AWESOME! The solution was to use WHERE a.department='Oncology' instead of AND a.department='Oncology'.

 

Thank you so much! :D