joining two tables in report

[lsantos]

Crystal Reports v7, Database Informix

I created a report that prints information from two tables. A documents table and a category table.

Documents table (fields: id, description,..., categ 1, categ 2, categ 3, categ 4)
Document records are classified with Category 1, Category 2, Category 3, Category 4, in a hierarchical fashion, so they have at least Category 1, and may have sub-category 2, 2 and 3 or 2 and 3 and and 4

Documents table might contain:
Row 1: 1,compaint 1, Animal, Dogs,Attack,
Row 2: 2,complaint 2,Animal, Dogs,,
Row 3: 3,complaint 3,Animal, Dogs, Nuisance, Bangalow
Row 4: 4,complaint 4, Water, Storm, Drains, Repair
Row 5: 5,complaint 5,Water

Category table (fields: categ 1, categ 2, categ 3, categ 4)
Categories can have various levels or sub-categories. Table content might be:
Row 1: Animal,,,,
Row 2: Animal,Dogs,,,
Row 3: Animal,Dogs,Attack,,
Row 4: Animal,Dogs,Nuisance,Bangalow
Row 4: Water
Row 5: Water,Storm
Row 6: Water,Storm,Drains,Repair

My problem is that the report only prints records that have a match on category 1, 2, 3 and 4 with category table. So in this example only rows 3 and 4 of documents table are included in the report. Records classified with only category 1 and 2 or 1, 2 and 3 are not included.

In my testing I changed the joint type in ‘Link Options’ under ‘Visual Linking Expert’ to left outer join or right outer join. That has made some difference as I am fetching all records I am supposed to. However all records that are not classified with category 1, 2, 3 and 4 are grouped on top of the report under a blank category 1, blank category 2, blank category 3 and blank category 4.

Any suggestions will be appreciated.

Luis
Mullumbimby, Australia

 

[pgtek]

Hi

Try to switch your table around and used left other join.
sometime it the first table that you used may cause this problem.
I worth a try

cheers

pgtek

 

[lbass]

Using the left outer join from Documents to Categories is the correct approach. You haven't explained what you want your final report to look like. The left join returns all records from Documents. If you only want to see those records from Documents where one of the Category fields is not null, then add something like the following to your record select:

not isnull({doc.cat1})

However, you should not add any selection criteria on the Category table, since that would nullify the left join.

It looks like you have a group on your report--how did you define that group? I have never used it, but you might want to try using report->hierarchical grouping options.

-LB