Re: Correct way to join and as well as preserve all parent table entries in a join with many tables!!

On Feb 14, 3:41 pm, radkri <chikkub..._at_gmail.com> wrote:
> This problem is quite interesting to me. I have asked this question to
> others but no body is able to provide me with proper answers.
>
> The problem is: How do I join a huge parent table with many child
> tables (more than 5 child tables) preserving all of the parent table
> entries. Lets say there is the parent table parentTable and three
> child tables childTable1, childTable2, childTable3. In order to get
> the data after joining these tables the query that I have been using
> was:
>
> select parent.field1, parent.field2, parent.field3, child1.field4,
> child1.field5, child2.field6, child3.field7 from ParentTable parent,
> childTable1 child1, childTable1 child2, childTable3 child3 where
> parent.fielda = child1.fieldb and parent.fieldc = child.fieldd and
> parent.fielde = child.fieldf.
>
> Although the tables are huge (more than 100,000 entries), this query
> is very fast, however those parent table entries which do not have
> child entries are lost. I know that I can left join a parent table
> with a child table and then with the next child table and then with
> the next child table and continue. Isn't there a simple solution for
> this commonly happening problem?
>
> Please provide suggestions please...

Simple solution: add (+) after the child column's name:   parent.fielda = child1.fieldb(+)
  and parent.fieldc = child.fieldd(+)
  and parent.fielde = child.fieldf(+)

The above is a left outer join using Oracle specific syntax.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Feb 14 2008 - 16:03:50 CST