my dog learned polymorphism*
The moose likes JDBC and the fly likes Best way to join a parent with many child tables yet preserving all parent table rows Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Best way to join a parent with many child tables yet preserving all parent table rows" Watch "Best way to join a parent with many child tables yet preserving all parent table rows" New topic
Author

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

Rama Krishna
Ranch Hand

Joined: Oct 16, 2007
Posts: 110
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...
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30356
    
150

Rama,
What you are trying is correct. You need to do multiple outer joins.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Rama Krishna
Ranch Hand

Joined: Oct 16, 2007
Posts: 110
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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Best way to join a parent with many child tables yet preserving all parent table rows