• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1908
  • Last Modified:

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.

0
ChiBella
Asked:
ChiBella
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should work better:
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';

0
 
ChiBellaAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, but I don't see anything wrong...
note that the LEFT JOIN syntax is preferred, and is eventually to give better results.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ChiBellaAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
silly me... I got the (+) on the wrong place :(
Select a.emp, b.manager
from emp a, manager b
where a.empid = b.empid(+)
and b.managerID (+) <> '123'

now, I don't know about the "wrong counts" is about...
0
 
ChiBellaAuthor Commented:
thanks...that works!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now