We help IT Professionals succeed at work.

Rows missing from one table

MaccaRay
MaccaRay asked
on
Hello Crystal experts,

As you?ll see I am an untrained Crystal Reports novice, and I could do with some advice.

Here is a simplified example to demonstrate what I am trying to do.

Say we have a customer table containing a row for EVERY customer. It contains just 2 columns, custno and custname.

So it might contain:

001 Smith
002 Jones
003 Brown

We also have an info table that contains various rows for various things we know about our customers. It contains 3 columns, custno, infotype and info. A customers may have none or one or many rows in this table. It might look like this:

001 credlim   100.00
001 address   44 Oak Street
001 haircolor Blonde
002 haircolor Red
003 credlim   200.00

I want a list of all my customers showing name, and if available, address.

I can't find a way to construct the report so it will show me ALL customers in the customer table, and show the corresponding address if there is one present in the info table. If there isn?t an address just show the notation ?no address?.

Depending on what I try, I either get a list that shows every infotype for every customer, or it only shows an entry for those customers who have an address infotype.

Also, can you recommend a good online Crystal reports training tutorial that I could buy?

Thanks for your help.

Iain MacRae
Comment
Watch Question

Commented:
You need to find out what a LEFT OUTER JOIN does.
Commented:
One surefire method goes like this

1. Create a report with customer table only.
2. create a report (for use as sub report later) which has a selection formula infotype='address'. This will filter the records to infotypes of addresses.
3. Now, use the first report, use 'insert sub report' option, and insert the second report. link the report on customer number field.

This will give you the report you want.


Method two
-----------

1. Create a report by linking both tables. link should start from customer table and end at the info table (link arrow direction)
2. Now, use a recordselection formula  infotype="address"

Again you will get your report

Method three
____________

change the sql. It is a very very simple sql. If you can't do it, ask me.

Author

Commented:
Thanks for your useful suggestions raj_nbr

Regards

Iain MacRae

Explore More ContentExplore courses, solutions, and other research materials related to this topic.