CODEWITHSUNDEEP
mysql
user2439492
user2439492

Reputation: 91

SQL statement with JOIN and WHERE clause

I'm new to SQL and trying to solve this problem with two tables Employee and Department. I want display the names of all the employees of 'HR' and 'Sales' departments. Tables are Employee (emp_id, emp_name, dept_id) and Department (dept_id, dept_name).

Thanks!

Upvotes: 3

Views: 819

Answers (4)

SonaMali
SonaMali

Reputation: 1

You can get like this, 

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'

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

As you only want to display employee data, only select from that table. The rest is criteria. You want their department to be either 'HR' or 'Sales', so the straight-forward way of writing this is the IN clause (you could 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 more easy to read than to join the tables first, only to use one as a filter for the other.

Upvotes: 2

Muhammad Atif
Muhammad Atif

Reputation: 1102

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

Upvotes: 1

Nikhil Batra
Nikhil Batra

Reputation: 3148

Try this:

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 the dept_id of Employee table and Department table. Those values matched will be returned. then out of all the fields you will select the emp_name and limit the employees belonging to HR and Sales department using the where clause.

Upvotes: 3

Related Questions