noobs here.

i have 2 master tables its employees and trainings each of that table has primary key and 1 table training_histories to mapping employee to training that have been taken. so my scheema looks like this.

table employees : id, employee_name, etc.

table trainings : id, training_name, etc.

table training_histories : id, employee_id, training_id

I want to do a query to find out which employees have been included in a particular training with the training_id parameter.

i try this query

SELECT * 
FROM employees AS emp 
LEFT OUTER JOIN employee_training_histories AS th ON emp.emp_id = th.emp_id 
LEFT OUTER JOIN trainings AS trn ON th.training_id = trn.training_id 
WHERE th.training_id = 1

but its not works like my expectations

i expect when training_id = 1 its showing like this

when training_id = 2 its showing like this

still showing all employee but training is depending on training_id

thanks for attention forgive me for my bad engglish..

CodePudding user response：

You need to switch from LEFT JOIN to INNER JOIN.You can see the difference between the various joins here.

SELECT * 
FROM employees AS emp 
INNER JOIN employee_training_histories AS th ON emp.emp_id = th.emp_id 
INNER JOIN trainings AS trn ON (th.training_id = trn.training_id AND th.training_id = 1)

P.S: You can directly place your filter in the ON clause for optimal results.