No left outer join in oracle 8!!!

hi,

let me know if the subject is wrong;

i got the following workaround for outer joins:
R left outer join S on R.B = S.C;
can be rewritten as:
select * from R, S where R.B = S.C(+);

what if i have a double left outer join
say A left outer join B on (..whatever) left outer join C on(..)

thanx in advance
regards

Let me try to help by quoting from the book "OCA/OCP Introduction to Oracle 9i SQL", pages 256 and 257:
A left outer join is a join between two tables that returns rows based on the matching condition, as well as unmatched rows from the table to the left of the JOIN clause. For example, the following query returns the country name and city name from the COUNTRIES and LOCATIONS tables, as well as the entire country names from the COUNTRIES table.

SELECT c.country_name, l.city
FROM countries c LEFT OUTER JOIN locations l
ON c.country_id = l.country_id

In pre-9i or traditional Oracle left-outer-join syntax, the query is written as follows:

SELECT c.country_name, l.city
FROM countries , locations l
WHERE l.country_id(+) = c.country_id

Hope it helps,
Dan

probably your query would be look like that:
SELECT e.first_name,e.last_name,d.department_id,d.department_name,l.location_id,l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id(+)
AND d.location_id = l.location_id(+)

An outer join (indicates by (+)) cannot be combined with another condition using the OR or IN logical operators.

If you need full outer join then use the SET operator to combine the result set of each outer join. As follows:

SELECT empno, ename, sal, deptno
FROM emp
WHERE emp.deptno (+)= deptno.deptn
UNION
SELECT empno, ename, sal, deptno
FROM emp
WHERE emp.deptno = dept.deptno(+)

thanx for the answers.

Hi,

I have a similar problem, but taken to the next level. This is my situation.

I have three tables, A, B and C.

I always have a record in A and I always want to have a recordset that returns this record.

Tables B and C may or may not have data.

Thus,

select a.field1, b.field1 from a, b where a.id1 = b.id1(+)

Works fine.

But when I add C, a lack of matching records in B (and C) will result in no rows returned.

Thus,

select a.field1, b.field1, c.field1 from a, b, c where a.id1 = b.id1(+) and b.id2 = c.id2(+)

does not work.

What is the most efficient SQL to return the matching A row and NULL for B and C?

Many thanks,

Martin

I have three tables:
T1(A,B)
T2(A, B, C) (C is null)
T3(A, C)(empty table).

This query:

returns T1's content, I think that your problem lies somewhere else, check you data for spaces or blanks instead of nulls.

Sorry, I obviously didn't make it clear. The problem arrises when T2 and T3 are both empty tables.

Thanks again,

Martin

Ok, I truncated T2 and redid the query, same results (as expected it returns T1's content).

hi ,

I have one doubt. If I want to check one column in the tabke T2 also then how can I do it ??

For example

I have three tables:
T1(A,B)
T2(A, B, C) (C is null)
T3(A, C)(empty table).

select t1.* from t1, t2, t3 where
t1.a = t2.a(+) and t2.b(+) = 'TEST' and t2.c = t3.a(+)....

is it correct...but it is not working ....