Difference between revisions of "Define Join Relationships"

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:

Left-Join
Right-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:

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.

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:

The Filter by Record Ownership option is commonly used for Reports based on Database Views.

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.

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.

instrument_ID	Instrument	player_ID	Players	Can Play Instrument
Object A		Object B
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:

instrument_ID	Instrument	Played by
Left-Join Report
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:

instrument_ID	Instrument	Played by
Right-Join Report
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:

instrument_ID	Instrument	Played by
Equi-Join Report
IN1	Guitar	Bill Bertie Kerry Kline
IN2	Keyboard	Madeline Morris Sissy Smith
IN4	Accordion	Madeline Morris Sissy Smith

@@ Line 1: / Line 1: @@
-Object relationships are defined by specifying fields to link in (a maximum of three) objects.
+Joins are defined by specifying the fields that link a record in one object to a record in another object.
-====Join Theory====
+=====About Joins=====
-In a join, choose at least one object that contains an ownership record. Good reasons to do this:
+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"
+! Row !! Record data from "A" !! Record data from "B"
+! align="left"| Type of 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]].
+|-
+| 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|<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=====
+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.
-:*[[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:
+:*The drop down lists fields that available for use in a Join.<br>In general, the following kinds of fields can be used:
-:** ID field (a field containing a record identifier)
+:** Record ID fields (fields containing a record identifier)
-:** [[Components]]
+:** [[Lookup]] fields
-:** All [[Lookup]] fields and [[Lookup-Derived Fields]]
+:** Fields that contain raw data values like number or string.
-:** Fields where the field name ends with '#'
-====Rules for Joins====
+{{Note|<br>One common convention for numeric identifier fields is to end them with '#'.
+<br>That convention can make it easier to identify fields that can be used for a join.
+<br>For example: <tt>SocialSecurity#</tt> or <tt>Account#</tt>.}}
+=====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:
+:{|
+<pre>
+A.id <join type> B.id
+A.id <join type> C.id
+</pre>
+|}
+or
+:{|
+<pre>
+A.id <join type> B.id
+B.id <join type> C.id
+</pre>
+|}
+where <tt><nowiki><join type></nowiki></tt> is <tt>Left</tt>, <tt>Right</tt> or <tt>Equi</tt>
- A.id <join type> B.id
+=====Example Join=====
- 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.
@@ Line 65: / Line 97: @@
 | || ||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=====
@@ Line 128: / Line 151: @@
 | || ||Gary Green
 |}
 =====Equi-Join=====

Search

where to start

Feedback

customize and design

resources

Tools

Personal tools