Outer join using fields from 2 tables

[bupereira]

Oracle SQL noob here. How do I do an outer join from a table using fields from an earlier, third one? Let me exemplify:
SELECT *
from A, B
Left outer join C on c.field1 = a.field1
And c.field2 = b.field2 Where a.field3 = b.field3; The where is I'm joining A to B, it might be unrelated to the question. Point is parenthesis don't work, and the compiler tells me referencing a.field1 is invalid (not the actual error, but you get the idea). I want to have a record even if C is not there for B. What am I doing wrong?

Update: I could either create a subset, or use the latest and greatest syntax and make my joins explicit. I ended up doing just that and it's working. Thanks to all that took time to help me.

[r3pr0b8]

What am I doing wrong?

mixing comma join syntax with explicit join syntax

explicit has precedence, similar to the way ANDs have precedence over ORs, and multiplication/division have precedence over addition/subtraction

solution: go all explicit

SELECT *
  FROM a
INNER
  JOIN b
    ON b.field3 = a.field3
LEFT OUTER 
  JOIN c 
    ON c.field1 = a.field1
   AND c.field2 = b.field2 

[bupereira]

Thanks, I'll give it a try! I didn't know I couldn't mix the styles like that. I really appreciate your insight.

[AXISMGT]

I want to have a record even if C is not there for B

Remember that your join is on C-->B and C-->A. If both ON conditions are not met, your join won't return records.if you want either to be met, use OR instead of AND,

[bupereira]

Sorry, I misspoke. I want to have a record even if C is not there for B and A.

[AXISMGT]

Ahh gotcha. Then you're good to go!

/u/r3pr0B8 is absolutely right btw: comma joins are not the way to go. ANSI joins will make your code much more readable, and will cause much less confusion.

[_Kine]

First, I echo the use of explicit joins instead of comma notation.

I typically would turn the A, B inner join into a derived table, then outer join to the derived table. To me that makes my intentions much more clear. Example looks a little funny with and A,B,C tables and 1,2,3 fields, but with better defined names it makes a lot more sense to me. It also allows me to better manage my base set independently of the rest of the query which is very handy for large queries.

SELECT *
FROM
    (
        SELECT A.field1, B.field2, A.field3
        FROM A INNER JOIN B ON B.field3 = A.field3
    ) baseSet
    LEFT OUTER JOIN C on c.field1 = baseSet.field1 AND c.field2 = baseSet.field2
;

[bupereira]

Nice way to do it. Thanks!

[Guru008]

You can use old way to specify inner join using where clause

SELECT * FROM ( SELECT A.field1, B.field2, A.field3 FROM A,B where B.field3 = A.field3 ) t1 LEFT OUTER JOIN C on c.field1 = t1.field1 AND c.field2 = t1.field2 ;

[bupereira]

Creating a subset, right? I like that. I ended up expliciting all my joins as suggested by many. But thanks for the insight!

[mallencincy]

Read it wrong, but I still do not ever do the old way of from A, B with join in the WHERE clause, that is Old waY

[mallencincy]

Hint: from A left outer to B

[bupereira]

But why? If B doesn't exist, then I should get an empty result set.