0

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..

Akina
  • 39,301
  • 5
  • 14
  • 25
Gunawan Suarna
  • 25
  • 4
  • 1
    The condition by right table must be placed into ON, not into WHERE. – Akina Nov 23 '21 at 08:43
  • You provide desired output without source data. Add source data which results in this outputs - as CREATE TABLE + INSERT INTO scripts. – Akina Nov 23 '21 at 08:45
  • You should read [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join), and, after reading, re-write your query. – Luuk Nov 23 '21 at 08:49
  • thanks for your comment @Akina , i use ON clause for optimal results. and i need to learn again about join consep – Gunawan Suarna Nov 23 '21 at 13:35

1 Answers1

1

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.
Salvino D'sa
  • 4,018
  • 1
  • 7
  • 19
  • thanks for the answer, i need to read more about join consept.. i use your PS about ON clouse and its works for my case, thankyou so much – Gunawan Suarna Nov 23 '21 at 13:33