There are 2 tables, Employee and Department. There are few records in employee table, for which, the department is not assigned. The output of the query should contain all th employees names and their corresponding departments, if the department is assigned otherwise employee names and null value in the place department name. What is the query?

Use an ouer join to get your query.

Select E.ENAME, D.DNAME

from Employee E, Dept D

where D.DEPTNO (+)= E.DEPTNO

 

 

-Rakesh

An outer join is correct, but try using the ANSII standard join syntax as it is more flexible with outer joins and filtering of the inner tables

select e.name As Employee, d.name as Dept
from

select name, department from employee left join department on employee.department_id = department.id

Select DeptNo, DName, Count(1)FROM Emp a, Dept bWHERE a.deptno*=b.deptnoGroup By DeptNo, DName

select e.ename,d.dname

from employee e,department d

where e.eid=d.eid(+);

select e.empname,d.dptname

from employee e,department d

where e.eid=d.eid(+);

 

Select a.ename, b.dname

from Emp a, Dept b

where a.deptno (+)= b.deptno

Hi All,

A bit lengthy but a good alternative for the JOINS...

 (Select
  E.Emp_Name as EmployeeName,
  D.Dept_Name as DepartmentName,
  D.Dept_Id as DepartmentID
 from
  #tmp_Emp E,
  #tmp_Dept D
 where
  E.Dept_Id = D.Dept_Id
 )
 UNION
 (Select
  E.Emp_Name as EmployeeName,
  DepartmentName = 'NULL',
  E.Dept_Id as DepartmentID
 from
  #tmp_Emp E
 where
  E.Dept_Id IS NULL
 )


Regards,
Maddie.

What Mr Rakesh written is, I think Oracle query,
Here is the Sybase (TSQL Query for that)

Select a.empid, b.departmentname from employee a, department b where a.empid *= b.empid

For Oracle:

Select a.ename, b.dname


from Emp a, Dept b

where a.deptno (+)= b.deptno

For Sybase it should be like this:


Select a.ename,
b.dname

from Emp a, Dept b

where a.deptno *= b.deptno

as all the rows from the Emp table are requested

Use the outerjoin  in your query.
For Ex:

select * from employee, department where employee.id *= department.id

select e.* from employee e
left outer join department d
on e.id_department = d.id_department

select a.emp_name,isnull(a.dept_nm,' ') from emp a,dept b
where a.dept_nm=b.debt_nm

Use a left outer join , it is going to pick all records in employee table and the corresponding records in department table if available

select e.emp,d.dept
from employee e, department d
where e.dept_id*=d.dept_id

SELECT Emp.ENAME, Dept.ENAMESELECT Emp.ENAME, Dept.ENAME

The query for this question can be written by using the outer join.

select* from employee a,department b where a.emp_id*=b.emp_id