Help needed with Report Design 1

[FoxAl]

I am using Crystal Reports, but the principle is independent of this.

I have three tables: A, B and C. (there are at least 5 tables in fact - but the essence is 3)

B and C relate to A but not to each other. A is related to B and C by left outer joins. There is a select on A so that only one row in A is selected. The relevant data in A, B and C is:

Table A:

A1

Table B

A1B1
A1B2

Table C
A1C1
A1C2
A1C3

The report has to display

A1
B1
B2
C1
C2
C3

The problem is that the complex records generated are

A1B1C1
A1B1C2
A1B1C3
A1B2C1
A1B2C2
A1B2C3

Using group headings (for example) I can get

A1
B1
C1
C2
C3
B2
C1
C2
C3

Ideas?

 

[MalcolmW]

What database are you using, and are you using ODBC? Malcolm

 

[clapag22]

You may be getting a cartesian product because there is no relationship between table b and table c.

Does your current query look like this:

Suppose the field that contains the value 'A1' has the same name in all three tables and is called field1.

select *
from tablea, tableb, tablec
where tablea.field1 *= tableb.field1
and tablea.field1 *= tablec.field1

("*=" is left outer join if not familiar with that syntax)

A union query may work better... something like this...

Again, suppose the field that contains the value 'A1' has the same name in all three tables and is called field1.

select distinct field1 from tablea
where field1 = 'A1'
UNION
select distinct someotherfield from tableb
where field1 = 'A1'
UNION
select distinct someotherfield from tablec
where field1 = 'A1'