Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unanswered: outer join a table with multiple tables

    In table1 we store for a entity the suppliers for a part.
    Table1
    --------
    entity
    part
    supplier

    In table2 we store the moq and sourcing route for a supplier
    Table2
    --------
    part
    supplier
    sourcing route
    moq

    In table3 we store the leadtime for a part based on the sourcing route and entity
    table3
    -------
    entity
    part
    sourcing route
    lead time.

    We need a report to list the missing moq's and lead time for a entity part, supplier, soucring route.

    I need to do something like
    select t1.*, t2.*
    from table1 t1, table2 t2, table3 t3
    where t2.part(+) = t1.part
    and t2.supplier(+) = t1.supplier
    and t3.part(+) = t2.part
    and t3.sourcing_route(+) = t2.soucring_route
    and t3.entity(+) = t1.entity

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: outer join a table with multiple tables

    ANSII sql will allow you to outer join numerous times.

    Try something like

    PHP Code:
    select t1.*, t2.* 
    from table1 t1 
       full outer join table2 t2 on t1
    .part t2.part 
                                       
    and t1.supplier t2.supplier
       full outer join table3 t3 on t2
    .part t3.part 
               
    and t2.sourcing_route t3.sourcing_route 
               
    and t1.entity t3.entity 

  3. #3
    Join Date
    Nov 2003
    Posts
    2
    Thanks. That helped me a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •