CODEWITHSUNDEEP
mysql
Gunawan Suarna
Gunawan Suarna

Reputation: 25

MySql join tables with filter at one of table

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

employee training
a javascript
b javascript
c null
d null

when training_id = 2 its showing like this

employee training
a null
b C++
c C++
d null

still showing all employee but training is depending on training_id

thanks for attention forgive me for my bad engglish..

Upvotes: 0

Views: 94

Answers (1)

Salvino D'sa
Salvino D'sa

Reputation: 4506

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.

Upvotes: 1

Related Questions