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.

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

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

I, think, this is what Pat was talking about.
Dave