SQL Language (MOSC)

MOSC Banner

How to eliminate dupliate records when joining 3 tables and one table has a one to many relationship

edited Sep 24, 2012 2:47AM in SQL Language (MOSC) 2 commentsAnswered
I need to select records via a join from three tables. Two of the tables have one-to-one relationship and the third has one-to-many relationship with each of the other two. I need to get single records from the join.

table1   <------->  table2

   |                          |

     |                     |

        |            |

        V         V

      table3

Here is what I've been working with:

SELECT   req.component AS file_type,
              comp.VALUE AS bbr_file_id,
              TO_DATE ('19700101', 'YYYYMMDD')
              + ( (MAX (NVL (request_time, 0))) / 1000 / 60 / 60 / 24)
                 AS time_sent,
              res.number_of_items_received AS total_items,
              comp2.VALUE AS cycle_date,
              req.job_id AS debug_code
       FROM ii_transaction_request req, ii_transaction_component comp, ii_transaction_component comp2, ii_transaction_response res

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center