Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    56

    Exclamation Unanswered: Outer join upto 5 tables

    Hello All,

    Am using oracle 11g.

    My requirement is to join max upto 5 tables and all the tabes are neeed to join to MTAB table as specified below.

    I have Five tables ECLA , ECLB , ECLC , ECLD, MTAB.

    All the ECL tables are master tables and MTAB is child table and its always possible that ECL tables have more records than MTAB and the tables are joined by famid, seqno and alcno .

    ECLA is a parent and MTAB is child
    similarly
    ECLB, MTAB
    ECLC, MTAB,
    ECLD, MTAB

    The user may select ECLA.X , ECLB.Y, ECLC.Z, ECLD.P , MTAB.Q from the tables


    the records are stored like
    ECLA - MTAB
    ECLB - MTAB
    ECLC - MTAB
    ECLD -MTAB



    I implemented the two table jon by the following sql statement.

    select ECLA.FAMID,
    ECLA.CLODESCA,
    ECLA.CLOINOUT,
    MTAB.COST,
    MTAB.ALCNO
    from P3I2008Q1.ECLA LEFT OUTER JOIN P3I2008Q1.MTAB ON
    (ECLA.FAMID = MTAB.FAMID and ECLA.SEQNO = MTAB.SEQNO and ECLA.ALCNO = MTAB.ALCNO)

    How I can implement if I take

    1) ECLA, ECLB , MTAB
    2) ECLA , ECLB, ECLC MTAB
    3) ECLA , ECLB, ECLC, ECLD, MTAB.

    appreciate your quick reply.

    thanks/kumar.
    Last edited by MIKELALA; 05-24-10 at 13:07.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If there is no relationship between ECLA, ECLB, ECLC, and ECLD then I'd recommend construction a separate SELECT statement for each table, then creating a UNION to combine the result sets of those SELECT statements.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2009
    Posts
    56

    outer join upto 5 tables..

    Hello PatP,

    Can you please give me an example ...for the tables specified taking some tablename.columname ...

    appreciate if you can reply to this..

    thanks/kumar

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I, think, this is what Pat was talking about.
    Code:
    select ECLA.FAMID
            ,ECLA.CLODESCA
            ,ECLA.CLOINOUT
            ,MTAB.COST
            ,MTAB.ALCNO 
        from P3I2008Q1.ECLA
    LEFT OUTER JOIN P3I2008Q1.MTAB
        ON ECLA.FAMID = MTAB.FAMID
       and ECLA.SEQNO = MTAB.SEQNO
       and ECLA.ALCNO = MTAB.ALCNO
    Union
    select ECLB.FAMID
            ,ECLB.CLODESCA
            ,ECLB.CLOINOUT
            ,MTAB.COST
            ,MTAB.ALCNO 
       from P3I2008Q1.ECLB
    LEFT OUTER JOIN P3I2008Q1.MTAB
        ON ECLB.FAMID = MTAB.FAMID
       and ECLB.SEQNO = MTAB.SEQNO
       and ECLB.ALCNO = MTAB.ALCNO
    UNION
    ...........
    Dave

Posting Permissions

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