CODEWITHSUNDEEP
sql
Anonymous
Anonymous

Reputation: 13

SQL query to find active employees

Sql query to find the active employees. sort the output in descending order based on employee id column and fetch only the first 15 records. Display employee id, employee name, city, state, country,salary, active date, status, department name, manager name. employee name: concatenate firstname & lastname with space character and fetch only first 10 letters from employee name manager name: display firstname and lastname(separated by space)

Tables:

the below given is the code which i used,

SELECT b.* FROM (SELECT e.emp_id, SUBSTR(e.emp_fname||' '||e.emp_lname,1,10) AS emp_name, l.city, l.state, l.country, e.emp_sal, e.emp_activefrom, e.emp_status,d.dept_name, d.dept_head  FROM employee e, location l, department d WHERe e.dept_id=d.dept_id AND e.loc_id=l.loc_id AND e.emp_status='Active' ORDER BY 1 desc) b WHERE rownum<=15;

I dont know what is wrong here. please help me out to solve this.

Upvotes: 0

Views: 5039

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Kazi is correct that you want to do this using JOINs. However you need to be careful about employees that have no managers when doing the JOIN. In addition, Oracle is finicky about limiting the number of rows -- and I assume you are using Oracle based on rownum comparisons. The correct method uses the fetch first clause.

So, you seem to be describing something like this:

select e.emp_id, substr(e.emp_fname || ' ' || e.emp_lname,1,10) AS emp_name,
       l.city, l.state, l.country,
       e.emp_sal, e.emp_activefrom, e.emp_status,
       d.dept_name, d.dept_head,
       (m.emp_fname || ' ' || m.emp_lname) as manager_name
from employee e join
     department d
     on e.dept_id = d.dept_id join
     location l
     on e.loc_id = l.loc_id left join
     employee m
     on e.Mgrid = m.EmpId
where e.emp_status = 'Active' 
order by e.EmpId desc
fetch first 15 rows only;

Upvotes: 0

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

It's always better use proper join for better understanding. To get the manager name self join (employee e inner jion employee m) is used here.

SELECT e.emp_id, SUBSTR(e.emp_fname||' '||e.emp_lname,1,10) AS emp_name, l.city, l.state, l.country, e.emp_sal, e.emp_activefrom, 
e.emp_status,d.dept_name, d.dept_head  , m.emp_fname||' '||m.emp_lname as manager_name
FROM employee e 
inner join department d on e.dept_id=d.dept_id 
inner join location l on e.loc_id=l.loc_id 
inner join employee m on e.Mgrid=m.EmpId

where e.emp_status='Active' and 
rownum<=15
order by e.EmpId desc

Upvotes: 0

Related Questions