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

From: radkri <chikkubhai_at_gmail.com>
Date: Thu, 14 Feb 2008 12:41:43 -0800 (PST)
Message-ID: <678e11e5-e1af-4c69-9446-d1c802127f98@v46g2000hsv.googlegroups.com>


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... Received on Thu Feb 14 2008 - 14:41:43 CST

Original text of this message