Rakesh Shankpal
Answered On : Mar 10th, 2006
Use an ouer join to get your query.
Select
E.ENAME, D.DNAMEfrom
Employee E, Dept Dwhere
D.DEPTNO (+)= E.DEPTNO
-Rakesh
Login to rate this answer.
Stephen
Answered On : 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
Login to rate this answer.
mary
Answered On : Mar 22nd, 2006
select name, department from employee left join department on employee.department_id = department.id
Login to rate this answer.
Tahir
Answered On : Jul 3rd, 2006
Select DeptNo, DName, Count(1)FROM Emp a, Dept bWHERE a.deptno*=b.deptnoGroup By DeptNo, DName
Login to rate this answer.
Himu
Answered On : Jul 27th, 2006
select e.ename,d.dname
from employee e,department d
where e.eid=d.eid(+);
Login to rate this answer.
select e.empname,d.dptname
from employee e,department d
where e.eid=d.eid(+);
Login to rate this answer.
Rajesh
Answered On : Aug 11th, 2006
Select a.ename, b.dname
from
Emp a, Dept bwhere
a.deptno (+)= b.deptno
Login to rate this answer.
maddie.march83
Answered On : 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.
Login to rate this answer.
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
Login to rate this answer.
Pratap Chavda
Answered On : 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
Login to rate this answer.
Use the outerjoin in your query.
For Ex:
select * from employee, department where employee.id *= department.id
Login to rate this answer.
select e.* from employee e
left outer join department d
on e.id_department = d.id_department
Login to rate this answer.
select a.emp_name,isnull(a.dept_nm,' ') from emp a,dept b
where a.dept_nm=b.debt_nm
Login to rate this answer.
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
Login to rate this answer.
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
Login to rate this answer.
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
Login to rate this answer.