Clever Geek Handbook

SQL statement with JOIN and WHERE clause

I am new to SQL and am trying to solve this problem with two Employee and Department tables. I want to show the names of all employees in the "HR" and "Sales" departments. The tables Employee (emp_id, emp_name, dept_id) and Department (dept_id, dept_name) .

Thank!

+3


source to share


4 answers


Try the following:

Select e.emp_name from 
Employee e inner join Department d
on e.dept_id = d.dept_id
Where d.dept_name in ('HR','Sales');


This query will compare table and dept_id

table . Matching values ​​will be returned. then from all the fields you will select and restrict employees belonging to the department and using the offer .Employee

Department

emp_name

HR

Sales

where

+3


source


As long as you want to display employee data, select only this table. The rest is criteria. You want their department to be either "HR" or "Sales", so the direct way of writing this is the IN clause (you can also use the EXISTS clause):

select emp_name
from employee
where dept_id in 
(
  select dept_id 
  from department
  where dept_name in ('HR', 'Sales')
);


I think this is easier to read than joining tables first and only using them as a filter for another.

+2


source 


select Employee.emp_name [Emplyee Name] from Employee inner join Department on Department.dept_id=Emplyee.emp_id where Department.dept_name='HR'
+1


source


You can do it,

SELECT [emp_name] FROM [dbo].[Employee] AS E
INNER JOIN [dbo].[Department] AS D
ON D.dept_id=E.dept_id
WHERE D.dept_name='HR' OR D.dept_name='Sales'
0


source






More articles:


All Articles