Displaying Missing Links

[MikeCopeland]

Using CR XI...
I am working with a SQL database that isn't "clean": that is, there are missing link values in various root records. For example:

Table1 Table2
F1 L1
1. 5-------5
2. 0 17
3. 17------^

In this example, every record in Table1 _should_ have a corresponding linked record in Table2. However, record #2 doesn't have a link to Tables 2 (it's 0), but most others do. Is there any way in CR to show anomalies such as this (display information from Table1 for record #2)? TIA

 

[lbass]

What makes "Record #2" number 2? You are showing a value of 17 in both tables, so that they would link together. In other words, why does the value 17 for table 2 belong to record #2 vs. record #3?

-LB

 

[MikeCopeland]

I'm sorry - my diagram doesn't explain. Okay, there are 3 records in Table1, and there are 2 in Table2. T1.R1 links to T2.R1, and T1.R3 links to T2.R2. T1.R2 has a 0 value in the link field to T2, and there's no 0-value record in T2. (My diagram doesn't really show this...).
So, the linkage I set up in CR doesn't have a value link for T1.R2, so the SQL query doesn't return anything for it. The query displays all information for T1.R1 & T1.R3, but can't complete the T1-T2 linkage for T1.R2.
I'm asking if there is any way (in CR XI) to produce a listing of the "T1.R2 without linkage to T2" situations. HTH...

 

[kskid]

Do a {T1.linkid} left outer joined to {T2.linkid}

In your record selection

isnull({t2.linkid})


In the detail section, the T1 record that is listed will not have any matching T2.

-lw