ChiBella
asked on
Oracle Left Outer Join Problem
Hello,
Can an Oracle pl/sql expert help me construct a left outer join in Oracle 9i that excludes some values in the join table?
Example:
Select a.emp, b.manager from emp a, manager b
where a.empid = b.empid(+)
and b.managerID <> '123';
How do I retrieve all employee info and all manager info except for managerid '123' in one query. The query above cancels out the outer join and limits the results to only the manager table.
Can an Oracle pl/sql expert help me construct a left outer join in Oracle 9i that excludes some values in the join table?
Example:
Select a.emp, b.manager from emp a, manager b
where a.empid = b.empid(+)
and b.managerID <> '123';
How do I retrieve all employee info and all manager info except for managerid '123' in one query. The query above cancels out the outer join and limits the results to only the manager table.
ASKER
Select a.emp, b.manager
from emp a, manager b
where a.empid = b.empid(+)
and b.managerID <> '123' (+)
generates an error...SQL command not properly ended.
from emp a, manager b
where a.empid = b.empid(+)
and b.managerID <> '123' (+)
generates an error...SQL command not properly ended.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
sorry, but I don't see anything wrong...
note that the LEFT JOIN syntax is preferred, and is eventually to give better results.
note that the LEFT JOIN syntax is preferred, and is eventually to give better results.
ASKER
The syntax is invalid. Cannot outer on a literal in Oracle. But your preferred sytax does seem to work, however I'm getting the wrong counts back.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks...that works!
Select a.emp, b.manager
from emp a, manager b
where a.empid = b.empid(+)
and b.managerID <> '123' (+)
note the better syntax using LEFT JOIN:
Select a.emp, b.manager
from emp a
left join manager b
on a.empid = b.empid
and b.managerID <> '123';