• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1898
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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