• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Best way to join a parent with many child tables yet preserving all parent table rows

 
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am using JDBC to join tables and get the resulset and save the results to a text file by applying more conditions on each row entries in the resultset. But I have a problem. 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...
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Rama,
What you are trying is correct. You need to do multiple outer joins.
 
Rama Krishna
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Actually, I found another good way out and it also worked like a charm. I only checked the field values and the size of the fields. But after you said left join, I am wondering if my solution was right again!

Instead of "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"

there is a continuous left join followed by a where clause as follows:

SELECT a.Field1, b.Field2, c.Field3, d.Field4
FROM Foo a
LEFT JOIN AdditionalTable d ON a.YetAnotherForeignKey = d.YetAnotherForeignKey
JOIN Bar b ON a.ForeignKey = b.ForeignKey
JOIN Baz c ON a.SomeOtherForeignKey = c.SomeOtherForeignKey
where a.Field1 > xyz;

This way the parent table will have all of its rows and those fields of child tables which are not available empty.. Thank you for your reply.
[ February 14, 2008: Message edited by: Rama Krishna ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic