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

    Exclamation Unanswered: joining upto 5 tables in oracle 11g

    Hello All,

    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.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post DDL (CREATE TABLE ....) for all 5 tables.

    can not compose SQL without knowing the columns involved
    Last edited by anacedent; 05-24-10 at 13:45.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2010
    Posts
    2
    You can join the same tables as many times as you want.
    But you have to use alias for the tables.
    Just like this :

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

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Wasn't this question answered satisfactorily the first time it was asked?
    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
  •