Table Linking Problems

[jbhsuman]

Crystal Version 9,

I am having trouble displaying records from linked tables. A common field, table.invoice, relates all the tables I am using and there are indexes using the invoice field as the key index. There are three tables that are linked:

Header (holds a single record of invoice header)
Detail (holds one to many records associated with the header)
History ( holds one to many records associated with the header)

Initially I have History linked to Header and everything works fine, however, when I tried to add the table Detail the report fails to display any data.

I have linked the tables as follows; History linked to Header and also linked to detail using the common field, invoice. I have also tried linking History to Header and Header to Detail, but in each case when I try to add a field from the table detail, the report does not display any data at all. If I remove the detail field, the report displays the data as before.

I think my problem is that I either do not properly understand the table linking hierarchy or I am not using the correct indexes. In older versions of CR8, I was able to go and set the index for the table, I do not see this feature in CR9. Does anyone know if you can set the index order in CR9 and can someone help me understand table-linking hierarchy?

Thanks.

 

[Turkbear]

Hi,
If no record in Detail matches the link condition, no data wil be displayed from ANY of the tables, unless you define the link to the Detail table as a 'Left Outer Join' .

Use the Visual Linking panel to set the link option.



I would recommend linking Header to History( EquiJoin) and to Detail( Left Outer).

 

[jbhsuman]

Thanks for responding. I am not familiar with the left (right) outer join function. Can you please explain it in more detail? Thanks

 

[lbass]

I would use two left joins: From header to detail, and from header to history. This allows for new cases where there is not yet any history.

-LB

 

[Turkbear]

Hi,
Sortof..

The 'left outer' join means to return the records in all tables 'to the left of' ( that is, linked from) this table even if no records in this table match the link condition.

I am not as familiar with right outer since I have never used it, but i expect it means return data from this table even if no matching records exist in tables that lionk to this one.
This may be a better definition:

left outer join ==>
outer join
<database> A less commonly used variant of the inner join relational database operation. An inner join selects rows from two tables such that the value in one column of the first table also appears in a certain column of the second table. For an outer join, the result also includes all rows from the first operand (&quot;left outer join&quot;, &quot;*=&quot;), or the second operand (&quot;right outer join&quot;, &quot;=*&quot;), or both (&quot;full outer join&quot;, &quot;*=*&quot;). A field in a result row will be null if the corresponding input table did not contain a matching row. 

For example, if we want to list all employees and their employee number, but not all employees have a number, then we could say (in SQL): 


	SELECT employee.name, empnum.number
	WHERE employee.id *= empnum.id


The &quot;*=&quot; means &quot;left outer join&quot; and means that all rows from the &quot;employee&quot; table will appear in the result, even if there is no match for their ID in the empnum table.

Hth,

 

[jbhsuman]

LB, Thanks for responding. Turk, thanks for the explaination. One more question, is the left outer join set only through a SQL statement? If so how do I set this parameter?

Thanks