ansi select statments and outer joins

[bookouri]

I know this is probably a stupid question, but
Im beginning to have to deal with more databases that are not oracle, so Im trying to learn to use ansi sql statements. In Oracle, I would normally do a select like this to get a list of all employees and their phones including employees who dont have an active phone.

select name, phone from emps, phones where
emps.emp_id = phones.emp_id (+)
and phones.active_flag(+)='Y'

How can I replicate that with an ansi style select? How can i get the outer join on phones.active_flag(+) = 'Y'?

I can do:
select name, phone
from emps
right outer join phones on (emps.emp_id=phones.emp_id)
where emps.active_flag = 'Y' and phones.active_flag='Y'

but that wont give me the emps who do not have active phones.

 

[SantaMufasa]

Bookouri,

Change "right" to "left" and see what happens:

select name, phone
from emps
left outer join phones on (emps.emp_id=phones.emp_id)
where emps.active_flag = 'Y' and phones.active_flag='Y'

Let us know your results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[bookouri]

I tried Left, Right and Full outer join, but the missing active_flag(+)='Y' is still stopping records from being displayed no matter which join version i tried. I've been researching ansi joins all day and havnt found any examples that were remotely like the active_flag(+)=yes problem. It may just not be possible in ansi sql.

 

[SantaMufasa]

My bad, boukouri, I spaced the "active_flag" issue. The way that I would solve it is:

select name, phone
from emps
left outer join phones on (emps.emp_id=phones.emp_id)
where emps.active_flag = 'Y'
  and (phones.active_flag='Y' or phones.active_flag is null);

Let us know how that works.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[bookouri]

Still no go.

 

[bookouri]

here's what seems to work though. I have to test it some more but it appears to work.

select ....
from emp
left outer join phones on (emp.person_id=phones.person_id and phones.active_flag='Y')
and emp.active_flag='Y'