SQL query help with outer joins

I am have 3 tables. I am using Oracle 10g.

I am trying to get records from Table A where rows don't exist in Table C AND if there is a corresponding row in Table C then it should meet condition where C_PR开发者_运维百科IAMRY = 1.

Table_A

A_ID, A_NAME, A_STATUS_CD, A_STATUS_DTE
----------------------------------------
1      test1       NM        
2      test2       BB
3      test3       CC

Table_B

B_ID     B_START_DATETIME     B_END_DATETIME
--------------------------------------------
10         07-10-2010          08-10-2010
20         07-10-2010          08-10-2010


Table_C

C_ID   A_ID   B_ID       C_PRIMARY
-------------------------------------
1       1       10          1
2       1       10          0
3       1       10          0



select A.A_ID, A.A_NAME, A.A_STATUS_CD, A.A_STATUS_DTE, B.B_ID, B.B_START_DATETIME, C.C_PRIMARY
   FROM TableA A, TableB B, TABLEC C
WHERE A.A_ID = C.A_ID (+) AND C.B_ID = B.B_ID(+) AND C.PRIMARY(+) = 1        

I wrote this query it doesn't work. Please suggest any ideas.

Thank you

---

Try this:

SELECT A.A_ID, A.A_NAME, a.A_STATUS_CD, A.A_STATUS_DTE,
   B.B_ID, B.B_START_DATETIME, C.C_PRIMARY
FROM TableA A LEFT JOIN TableC C ON A.A_ID = C.A_ID
   LEFT JOIN TAbleB B ON B.B_ID = C.B_ID
WHERE C.A_ID IS NULL OR C.PRIMARY = 1

---

Consider rewriting from (+):

select * from A, B where A.ID = B.A_ID(+)

To the equivalent, more readable left join:

select * from A left join B on A.ID = B.A_ID

Applying this to your query, I get:

select  *
from    TableA A
left join
        TableC C
on      c.a_id = a.a_id
        and C.PRIMARY = 1
left join
        TableB B
on      b.b_id = c.b_id

This should retrieve all rows from A, matched with rows from C where primary = 1, matched with rows from B.

To exclude rows from the result when no match is found, use an inner join. For example, to find rows in A, for which a corresponding row in C exists with primary = 1, but no corresponding rows in B:

select  *
from    TableA A
inner join
        TableC C
on      C.A_ID = A.A_ID
        and C.C_PRIMARY = 1
where   not exists
        (
        select  *
        from    TableB B
        where   C.B_ID = B.B_ID
        )