AgileApps Support Wiki Pre Release

Difference between revisions of "Define Join Relationships"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
 
(12 intermediate revisions by the same user not shown)
Line 1: Line 1:
Joins are defined by specifying the fields that link a record in one object to a record in another object.  
Joins are defined by specifying the fields that link a record in one object to a record in another object.  


====About Joins====
=====About Joins=====
A "join" is a concatenation of fields from two objects that have the same data for a field they have in common (even though the field may have different names). So if object A has records values 1, 2, 3, 4 in field "x", and object "B" has values 3, 4, 5, 6 in field "y", then a "join" produces rows like the following:
A "join" is a concatenation of fields from two objects that have the same data for a field they have in common (even though the fields generally have different names).  
 
There are three types of joins:
:* [[#Left-Join|Left-Join]]
:* [[#Right-Join|Right-Join]]
:* [[#Equi-Join|Equi-Join]]
 
For example, if object A has records with values 1, 2, 3, 4 in field "x", and object "B" has values 3, 4, 5, 6 in field "y", then the different kinds of "joins" produce rows like the following:
:{| border="1" cellpadding="5" cellspacing="1"
:{| border="1" cellpadding="5" cellspacing="1"
! Row !! Fields in "A" !! Fields in "B" !! Type of Join
! Row !! Record data from "A" !! Record data from "B"  
|-
! align="left"| Type of Join
| 1<br>2 || x=1,...<br>x=2,... || none<br>none || Rows returned in a "left join"
 
|-
|-
| 3<br>4 || x=3,...<br>x=4,... ||  y=3,...<br>y=4,... || Rows returned in a left, right, or equi-join.
| align="center"| 1<br>2 || x=1, ...field data...<br>x=2, ...field data... || none<br>none
|-
| Non-matching rows, returned only in a [[#Left-Join|Left-Join]].
| 5<br>6 || none<br>none ||  y=5,...<br>y=6,... || Rows returned in a right join.
 
|-  
| align="center"| 3<br>4 || x=3, ...field data...<br>x=4, ...field data... ||  y=3, ...field data...<br>y=4, ...field data...
| Matching rows returned in a ''left join'' or ''right join''.<br>The only rows returned by an [[#Equi-Join|Equi-Join]] (typically the default).
 
|-  
| align="center"| 5<br>6 || none<br>none||  y=5, ...field data...<br>y=6, ...field data...
| Non-matching rows, returned only in a [[#Right-Join|Right-Join]].
|}
|}


{{Important|A joining field should be indexed, for performance.}}
{{Important|<br>The "joining fields" should be indexed, for performance. In the example above,<br>field "x" should be indexed in A, and field "y" should be indexed in B.<br>''Learn more:'' [[Indexes]]}}


====Join Theory====
=====Join Theory=====
In a join, choose at least one object that contains an ownership record. The reason:
In a join, choose at least one object that contains an ownership record. The reason:
:*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 31: Line 45:
<br>For example: <tt>SocialSecurity#</tt> or <tt>Account#</tt>.}}
<br>For example: <tt>SocialSecurity#</tt> or <tt>Account#</tt>.}}


====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 50: Line 64:
where <tt><nowiki><join type></nowiki></tt> is <tt>Left</tt>, <tt>Right</tt> or <tt>Equi</tt>
where <tt><nowiki><join type></nowiki></tt> is <tt>Left</tt>, <tt>Right</tt> or <tt>Equi</tt>


====Example Join====
=====Example Join=====
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 83: Line 97:
| || ||P6||Gary Green||
| || ||P6||Gary Green||
|}
|}
==== Using Joins ====
Joins are a means to relate two objects (tabs) in order to build reports containing fields from both objects. There are three types of joins:
:* [[#Left-Join|Left-Join]]
:* [[#Right-Join|Right-Join]]
:* [[#Equi-Join|Equi-Join]]


=====Left-Join=====
=====Left-Join=====

Latest revision as of 00:16, 25 January 2012

Joins are defined by specifying the fields that link a record in one object to a record in another object.

About Joins

A "join" is a concatenation of fields from two objects that have the same data for a field they have in common (even though the fields generally have different names).

There are three types of joins:

For example, if object A has records with values 1, 2, 3, 4 in field "x", and object "B" has values 3, 4, 5, 6 in field "y", then the different kinds of "joins" produce rows like the following:

Row Record data from "A" Record data from "B" Type of Join
1
2
x=1, ...field data...
x=2, ...field data...
none
none
Non-matching rows, returned only in a Left-Join.
3
4
x=3, ...field data...
x=4, ...field data...
y=3, ...field data...
y=4, ...field data...
Matching rows returned in a left join or right join.
The only rows returned by an Equi-Join (typically the default).
5
6
none
none
y=5, ...field data...
y=6, ...field data...
Non-matching rows, returned only in a Right-Join.

Warn.png

Important:
The "joining fields" should be indexed, for performance. In the example above,
field "x" should be indexed in A, and field "y" should be indexed in B.
Learn more: Indexes

Join Theory

In a join, choose at least one object that contains an ownership record. The reason:

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
  • The drop down lists fields that available for use in a Join.
    In general, the following kinds of fields can be used:
    • Record ID fields (fields containing a record identifier)
    • Lookup fields
    • Fields that contain raw data values like number or string.

Notepad.png

Note:
One common convention for numeric identifier fields is to end them with '#'.
That convention can make it easier to identify fields that can be used for a join.
For example: SocialSecurity# or Account#.

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

Example Join

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
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 a record 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