How to get all columns from one table and only one column from another table with ID ? - MySql

Question

I have table employee which contains about 9 columns . ' id,name,etc..'

and I have another table 'onCall' contain 3 columns 'employee_id,department_id and rank '

what I want is to retrieve the employee data who is registered as OnCall employee on this department

I try this to get the employee data :

Select * from employee where id in (SELECT employee_id FROM onCall where department_id = 3)

But like this I can't know what is the rank of the onCall employee , is he registered as primary or backup ,how can I merge rank column from onCall table but only for the selected employee by the id

I tried to join them but I get syntax error

any way to solve this ?

Answer 1

This calls for an inner join

select EMP.*, OC.*
from EMPLOYEE EMP
inner join ONCALL OC
on OC.EMPLOYEE_ID = EMP.ID
where OC.DEPARTMENT = 3