Difference between revisions of "Define Join Relationships"
imported>Evelyn m |
imported>Aeric |
||
| Line 1: | Line 1: | ||
Object relationships are defined by specifying fields to link in (a maximum of three) objects. | Object relationships are defined by specifying fields to link in (a maximum of three) objects. | ||
===Join Theory=== | ====Join Theory==== | ||
In a join, choose at least one object that contains an ownership record. Good reasons to do this: | In a join, choose at least one object that contains an ownership record. Good reasons to do this: | ||
*The [[Report_Filter#Filter_by_Record_Ownership|Filter by Record Ownership]] option is commonly used for Reports based on Database Views. | *The [[Report_Filter#Filter_by_Record_Ownership|Filter by Record Ownership]] option is commonly used for Reports based on Database Views. | ||
| Line 15: | Line 15: | ||
** Fields where the field name ends with '#' | ** Fields where the field name ends with '#' | ||
===Rules for Joins=== | ====Rules for Joins==== | ||
When creating joins, note that each join is dependent on the previously defined join. | When creating joins, note that each join is dependent on the previously defined join. | ||
| Line 32: | Line 32: | ||
'''Note:''' Self joins are not permitted; The same object cannot be used on both sides of the join | '''Note:''' Self joins are not permitted; The same object cannot be used on both sides of the join | ||
===Example Objects=== | ====Example Objects==== | ||
In this example, we have a two objects: instruments and players in a band. As you might expect, the players in the band might play multiple instruments, and multiple instruments may be available to the band members. As is true with many bands, this band needs a drummer, and some players do vocals only. | In this example, we have a two objects: instruments and players in a band. As you might expect, the players in the band might play multiple instruments, and multiple instruments may be available to the band members. As is true with many bands, this band needs a drummer, and some players do vocals only. | ||
| Line 66: | Line 66: | ||
|} | |} | ||
=== Using Joins === | ==== Using Joins ==== | ||
Joins are a means to relate two objects (tabs) in order to build reports containing fields from both objects. Three join types are available: | Joins are a means to relate two objects (tabs) in order to build reports containing fields from both objects. Three join types are available: | ||
| Line 75: | Line 75: | ||
====Left-Join==== | =====Left-Join===== | ||
When objects A and B are related with a Left-Join, the report includes all records in object A, and all matching records in object B. Using the example above, the resulting report would include these records: | When objects A and B are related with a Left-Join, the report includes all records in object A, and all matching records in object B. Using the example above, the resulting report would include these records: | ||
| Line 101: | Line 101: | ||
|} | |} | ||
====Right-Join==== | =====Right-Join===== | ||
When objects A and B are related with a Right-Join, the report includes all records in object B, and all matching records in object A. Using the example above, the resulting report would include these records: | When objects A and B are related with a Right-Join, the report includes all records in object B, and all matching records in object A. Using the example above, the resulting report would include these records: | ||
| Line 130: | Line 130: | ||
====Equi-Join==== | =====Equi-Join===== | ||
When objects A and B are related with an Equi-Join, the report includes all records where a record in object B is related to records in object A. Using the example above, the resulting report would include these records: | When objects A and B are related with an Equi-Join, the report includes all records where a record in object B is related to records in object A. Using the example above, the resulting report would include these records: | ||
| Line 154: | Line 154: | ||
Sissy Smith | Sissy Smith | ||
|} | |} | ||
Revision as of 22:38, 22 July 2011
Object relationships are defined by specifying fields to link in (a maximum of three) objects.
Join Theory
In a join, choose at least one object that contains an ownership record. Good reasons to do this:
- The Filter by Record Ownership option is commonly used for Reports based on Database Views.
- Visibility of Reports in folders can also be managed with record ownership
About creating joins:
- When two objects are selected, a single, mandatory relationship (Join) must be specified
- When three objects are selected (e.g. A, B and C), two mandatory relationships must be specified
- Joins between the following fields are allowed:
- ID field (a field containing a record identifier)
- Components
- All Lookup fields and Lookup-Derived Fields
- Fields where the field name ends with '#'
Rules for Joins
When creating joins, note that each join is dependent on the previously defined join.
For example, consider the case where we have three tables: A, B and C. In these joins, the first join row defines the possibilities for the second row:
A.id <join type> B.id A.id <join type> C.id or A.id <join type> B.id B.id <join type> C.id where <join type> is Left, Right or Equi
Note: Self joins are not permitted; The same object cannot be used on both sides of the join
Example Objects
In this example, we have a two objects: instruments and players in a band. As you might expect, the players in the band might play multiple instruments, and multiple instruments may be available to the band members. As is true with many bands, this band needs a drummer, and some players do vocals only.
|
Object A |
Object B | |||
| instrument_ID | Instrument | player_ID | Players | Can Play Instrument |
|---|---|---|---|---|
| IN1 | Guitar | P1 | Bill Bertie | IN1 |
| IN2 | Keyboard | P1 | Bill Bertie | IN4 |
| IN3 | Drums | P2 | Madeline Morris | IN2 |
| IN4 | Accordion | P2 | Madeline Morris | IN4 |
| P3 | Sissy Smith | IN2 | ||
| P3 | Sissy Smith | IN4 | ||
| P4 | Kerry Kline | IN1 | ||
| P5 | Crissy Clemens | |||
| P6 | Gary Green | |||
Using Joins
Joins are a means to relate two objects (tabs) in order to build reports containing fields from both objects. Three join types are available:
Left-Join
When objects A and B are related with a Left-Join, the report includes all records in object A, and all matching records in object B. Using the example above, the resulting report would include these records:
|
Left-Join Report | ||
| instrument_ID | Instrument | Played by |
|---|---|---|
| IN1 | Guitar | Bill Bertie
Kerry Kline |
| IN2 | Keyboard | Madeline Morris
Sissy Smith |
| IN3 | Drums | |
| IN4 | Accordion | Madeline Morris
Sissy Smith |
Right-Join
When objects A and B are related with a Right-Join, the report includes all records in object B, and all matching records in object A. Using the example above, the resulting report would include these records:
|
Right-Join Report | ||
| instrument_ID | Instrument | Played by |
|---|---|---|
| IN1 | Guitar | Bill Bertie
Kerry Kline |
| IN2 | Keyboard | Madeline Morris
Sissy Smith |
| IN4 | Accordion | Madeline Morris
Sissy Smith |
| Crissy Clemens | ||
| Gary Green | ||
Equi-Join
When objects A and B are related with an Equi-Join, the report includes all records where a record in object B is related to records in object A. Using the example above, the resulting report would include these records:
|
Equi-Join Report | ||
| instrument_ID | Instrument | Played by |
|---|---|---|
| IN1 | Guitar | Bill Bertie
Kerry Kline |
| IN2 | Keyboard | Madeline Morris
Sissy Smith |
| IN4 | Accordion | Madeline Morris
Sissy Smith |