SQL & PL/SQL

Hi All,

I am new to Oracle..I have two tables as follow:

TableA(EmpID, Empname)

TableB(EmpID, DeptID, DeptName). A employee can work in multiple dept.

table A has 1 row as EmpID 1 and TableB has two rows for EmpID 1.

I have a search page which takes dept ID as parameter and return employee name...Dept ID is not mandatory feld for Search. If DeptID is passed it should return the employee who belongs to that Dept otherwise it should return 1 employee..

Now I have used as follow:

:IN_DEPTID is passed as parameter

Select EmpName from TableA Left Join tableB on a.EmpID = B.EmpID
WHERE
CASE WHEN :IN_DEPTID IS NULL
IN_DEPTID IS NULL
ELSE|
DEPTID = :IN_DEPTID

Now problem is when Dept ID is passed it correctly returns a row but if no deptid is passed it returns 2 rows but i want ony one row as there is only one employee.

Please help;

Edited by: user10239708 on 10 Sep, 2008 12:24 AM

Edited by: user10239708 on 10 Sep, 2008 12:28 AM