I am trying to write a query, where I am retrieving employee data, fname, salary and unit no., where fname is ‘Khalid’ and his department name should be either ‘IT’ or ‘Development’.
I am writing the query as
Select fname, salary, unitno from Employee Where fname = Khalid and Department.name = 'IT' or 'Development'
Both table have relationship based on Department number.
For Example:
Employee Table:
Fname salary unitno Dno Angel 2000 5 2 Jame 1000 3 2 Khalid 1500 6 4 Khalid 2500 9 2
Department Table:
Dnumber Name 2 IT 2 IT 4 Development 2 IT
Now the two table is in a relationship. My query is not using Dno and Dnumber to connect them and finding the accurate result. Do I need to use JOIN here? and how I know which is left table and which is right table?
Advertisement
Answer
You can join and filter:
select e.fname, e.salary, e.unitno, d.name from employee e inner join department d on d.dnumber = e.dno where e.fname = 'Khalid' and d.name in ('IT', 'Development')
Alternatively, you can also use a correlated subquery for filtering, if you don’t need to display the department name:
select e.fname, e.salary, e.unitno from employee e where e.fname = 'Khalid' and exists ( select 1 from department d where d.dnumber = e.dno and d.name in ('IT', 'Development') )