Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-04-25 : 08:17:01
|
| Jeff writes "Currently using SQL Server 2000.I need a good explanation of how you code multiple joins when the primary key is made up of multiple fields and there are many to one relationships between some of the tables and one to one between others. Does the order of the joins matter? Can I force certain joins to go first? Sometimes I need T1 joined to T2 AND T1 joined to T3. Sometimes I need T1 joined to T2 AND T2 joined to T3. I developed one report that needed four joins, but I couldn't figure it out so I created intermediate tables. This system has 14 main tables, but here is a short example of a common join required for a report. 3 tables:T1 (Clients)LenderBatchSequenceSequenceNameSequenceAddressT2 (Issues)LenderBatchSequenceIssueNumberT3 (IssueMaster)IssueNumberIssueDescriptionIssueRatingLender is a unique ID for each client. Batch is that client's info for a particular month/Year and Sequence is a unique id for each record of client data for that month/year. So LenderId+Batch+Sequence is the Primary Key for T1. There is a one to many relationship between T1 and T2 for LenderId+Batch+Sequence so T2 has LenderId+Batch+Sequence+IssueNumber for its primary key. Finally there is a many to one between T2 and T3. The primary key for T3 is just IssueNumber.For a particular Lender and Batch I need to link T1 to T2 to get all the issues and then T2 to T3 to get the IssueDescription and Rating. The Lender and Batch are entered by the user.All the examples of multiple table joins I found only have one field linking each table and they don't seem to explain if the order of the joins matters. In this case I want all records from T1 regardless of matches in T2 and T3. But sometimes I only want records from T1 if there is a match in T2. Also, it seems as if SQL requires a certain order to the "ON field=Field" clause. HELP!Thanks - Jeff" |
|
|
|
|
|
Multiple table joins with multiple fields