How to join (sligtly) unrelated tables

[cgv]

I hope I can articulate the question here... Thanks in advance for any commants at all - Im pulling my hair out.

I have 3 tables A, B, & C.

A->B relationship works fine. Set of records looks like:

a b1
a b2
a b3

A->C join also works fine. Set of records looks like:

a b2c1
a b2c2
a xxc3

Now the problem- I need to summarize A, B & C on the same report.

If I try to join and subtotal the three tables in crystal (or in a SQL view) I get:

A B C
===============
a b1 null
a b2 b2c1
a b2 b2c2

b2 is repeated in the resulting rows because multiple records exist for 'a' in table C. Summarizing this give me (in this case) an aparent doubling of b2.

What I would like to see is:
...
a b2 b2c1
a null b2c2
...

Does that make any sense, and any suggestions to compensate for this?

Thank you!

 

[bduke]

If A and B has are related by a key then you want A Full outer join with B and then you want A full outer join with C This should give you all entries in A and any relation you have with B and C hope this helps.


If this doesn't work send the SQL statement and I will code for you.


Happy Programming.

 

[cgv]

bduke,

Thank you for the response, appreciated.

The joins work fine (sortof), the problem is that the resulting set of rows appears to duplicate some records in table B as described above.

If you notice from the example above, the 'b2' record is duplicated twice - once for each corresponding record in table C. What I would like to see is blank or null values for B where 'orphan' rows in C exist... if that makes sense.

thx.

 

[FVTrainer]

Your diagram of your results is good, but I think it would be helpful if you showed what your actual joins are (what is the sql query, as shown in Database | Show SQL?). Also, what version of Crystal are you using? And, is using a stored procedure an option for you?

 

[cgv]

Thanks.

Without knowing enough about SQL, I suspect joining isnt the problem. The problem is that the outer joins seem to create resulting rows where (for example) a single record value in table b is replicated for each record found in c. Thats not what I want.

b
a /
c

In any case - It took a while since I did not know how to create a SP to do it... but in the end it worked.

first inserted all records from the a->b join, and then inserted the rews from the a->c join.

Pain in the butt to white it, but it works fine.

Thanks,