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?
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 ]
subject: Best way to join a parent with many child tables yet preserving all parent table rows