I have one main table that contains a TranID and some other fields. Then, I have about five tables that have detail information for each of the records in the main table. There is a one to one relationship between a detail record and the main record. The reason they are split between several detail tables is because the detail information is different based on TypeID.
My question is this: How can I join more than one of the detail tables?
This returns nothing....can someone explain why and hopefully provide a solution?
select * from MainTable m
Inner Join DetailTable1 d1 on m.TranID = d1.TranID
Inner Join DetailTable2 d2 on m.TranID = d2.TranID
Inner Join DetailTable3 d3 on m.TranID = d3.TranID
Inner Join DetailTable4 d4 on m.TranID = d4.TranID
Inner Join DetailTable5 d5 on m.TranID = d5.TranID
Thanks very much...If it returns nothing then one or more of the inner join conditions conditions are not met. That would be something like no d1TranID matching any m.TranID. Because they are all inner joins it only takes one bad join condition to break the whole thing.
To diagnose the problem you might try using Left Outer Joins and Right Outer Joins to see where matches are not being made between MainTable and each of the DetailTables. Something like:
Select m.TrandID,d1,TranID,d2,TranID,d3,TranID,d4,TranID,d5,TranID
from MainTable m
Left Outer Join DetailTable1 d1 on m.TranID = d1.TranID
Left Outer Join DetailTable2 d2 on m.TranID = d2.TranID
Left Outer Join DetailTable3 d3 on m.TranID = d3.TranID
Left Outer Join DetailTable4 d4 on m.TranID = d4.TranID
Left Outer Join DetailTable5 d5 on m.TranID = d5.TranID
and then to see where the detail table(s) have TranIDs that the MainTable doesn't have:
Select m.TrandID,d1,TranID,d2,TranID,d3,TranID,d4,TranID,d5,TranID
from MainTable m
Right Outer Join DetailTable1 d1 on m.TranID = d1.TranID
Right Outer Join DetailTable2 d2 on m.TranID = d2.TranID
Right Outer Join DetailTable3 d3 on m.TranID = d3.TranID
Right Outer Join DetailTable4 d4 on m.TranID = d4.TranID
Right Outer Join DetailTable5 d5 on m.TranID = d5.TranID
This should indicate what the problem(s) are.
No comments:
Post a Comment