Optional Data Linkage - Possible? 1

[MikeCopeland]

Using CR XI...
I suppose this involves some esoteric SQL linkage that I don't know how to do - or it's something I can do with a formula...but I need some help. Here's my problem:
I have several tables linked in a report, but one of the tables doesn't have linked records for some data I wish to see and use. For example:

T1.key T2.key T2.Data
1------->1 Cat
2------->2 Dog
3
4------->4 Bird

Here I need to see data for all 4 records, but there isn't a record in T2 for the 3rd T1 record. (Note that there is data in other linked tables for all 4 records.) In this scenario, I see the data for records 1, 2 and 4, and if I remove the T2 database linkage, I then see the (other) data for record #3.
My question: is there some way I can set up an "optional link" for T2 - and perhaps do something in a formula that produces a default value - so that I can see all the records and whatever data is available? TIA

 

[jdemmi]

I am assuming that you intended to include a record in T1.key? If so, in this design you need to use a FULL OUTER JOIN or a RIGHT OUTER JOIN

"A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side"

[edit] Right outer join
A right outer join (or right join) closely resembles a left outer join, except with the tables reversed. Every record from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A.

"A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate)."

http://en.wikipedia.org/wiki/Join_(SQL)#Full_outer_join

In CR desinger you can modify the join type by going to DATABASE EXPERT, select the LINKS tab and double click between tables T1 an T2

-- Jason
"It's Just Ones and Zeros

 

[lbass]

You need a left outer join FROM T1 TO T2, with no selection criteria on any T2 fields.

-LB

 

[MikeCopeland]

I believe I have that situation (I changed the link from its default of "Inner Join" to "Left Outer Join", and there is no selection criteria for anything in the T2 table), but it doesn't pull the records from T1 that don't have corresponding linkage to the T2 table. IOW, I'm not seeing the T1 table information for T1.Key 3.
Perhaps I haven't described my problem well enough: I want to display data from T1 (based on selection criteria from it) and display corresponding data from T2 where it exists. If T2 doesn't have a link from T1, I want to show "nothing" in thos columns. It's important to show _all_ T1 data that matches the selection criteria, because I need to show situations where T2 data is missing.
This isn't a case of T1 having "null" linkage to T2, but one where this linkage is from a common field ("ClientKey") that exists in both tables _if_ T2 has relevant data. If T2 doesn't have data, there's no record in the T2 table, but the T1 ClientKey field exists there nonetheless.
I was hoping to find a way to show all T1 data and show T2 data where T2 records exist in that table. Hope this clarifies my problem. TIA

 

[jdemmi]

I inadvertently suggested a right outer join, that was my mistake. But lbass was on the money with the left outer join. (The full outer should work as well but isn't necessary).

My guess is that your selection criteria is inadvertenly filtering out the records that have no data in T2.

As a test try removing your selection criteria.

Otherwise post a small sample of your actual data and your full selection criteria.

-- Jason
"It's Just Ones and Zeros

 

[lbass]

Mike, the left outer join absolutely should work, as long as there are no selection criteria on T2, so as Jason suggested, maybe you should show us you selection criteria. It might also help to see your query in database->Show SQL Query.

-LB

 

[MikeCopeland]

Yes, it does work - it's taken me a couple of days to rework the report as needed, and everything is now good. Perhaps I learned something here... 8<}}