Rakesh Shankpal

• Mar 10th, 2006

 

Use an ouer join to get your query.

Select E.ENAME, D.DNAME

from Employee E, Dept D

where D.DEPTNO (+)= E.DEPTNO

 

 

-Rakesh

Stephen

• Mar 21st, 2006

 

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

mary

• Mar 22nd, 2006

 

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

Tahir

• Jul 3rd, 2006

 

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

Himu

• Jul 27th, 2006

 

select e.ename,d.dname

from employee e,department d

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

Himu

 Profile Answers by Himu Questions by Himu

• Jul 27th, 2006

 

select e.empname,d.dptname

from employee e,department d

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

Rajesh

• Aug 11th, 2006

 

 

Select a.ename, b.dname

from Emp a, Dept b

where a.deptno (+)= b.deptno

maddie.march83

• Mar 29th, 2007

 

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.

shamimaziz

 Profile Answers by shamimaziz 

• Jul 21st, 2007

 

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

Pratap Chavda

• Aug 17th, 2007

 

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

tumeshk

 Profile Answers by tumeshk 

• Jun 14th, 2008

 

Use the outerjoin  in your query.
For Ex:

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

salilmulay

 Profile Answers by salilmulay 

• Nov 11th, 2009

 

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

santhanakumari

 Profile Answers by santhanakumari 

• Dec 1st, 2009

 

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

ajitmishra1983

 Profile Answers by ajitmishra1983 

• Jan 25th, 2010

 

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

kevinkpatel99

 Profile Answers by kevinkpatel99 

• Feb 21st, 2010

 

ANSI Syntax:

SELECT Emp.ENAME, Dept.ENAME

FROM Employee Emp left join Department Dept

ON Emp.DEPTNO = Dept.DEPTNO

Transact-SQL Syntax:

SELECT Emp.ENAME, Dept.ENAME

FROM Employee Emp, Department Dept

WHERE Emp.DEPTNO *= Dept.DEPTNO

       

Use *= for left outer join and =* for right outer join.

Thanks,

Kevin

suresh1_saxena

 Profile Answers by suresh1_saxena 

• Aug 6th, 2010

 

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