Outer Joint Issue 1

[adubrovs]

Hi,

I'm having trouble with left outer joint. I have two tables,
Table 1 and Table 2. I need to compare both to make sure that the a record is in both. If no matching record exists I need to see a blank for that field. I tried using isnull, but i'm not getting the desire result. And left outer joint is not working as I have filters set up on the two tables as well.

Table 1 Table 2
Record1 w w
Record2 x
Record3 y y
Record4 z

Thanks.

 

[hilfy]

You're not going to be able to do this with a left outer from Table 1 to Table 2. One way to do this would be if you have a "master" table between the two that has all of the data for your "record" field above. You would then left outer join from that master table to each of your two tables. If there is only zero or one record in each of Table 1 and Table 2 for your master data, this becomes easy - group on the identifying info in the master table and put all of the data, including the fields from Table 1 and Table 2 in the group header. Suppress the details section and you should get what you're looking for.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[RTag]

You can use FULL OUTER JOIN

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[lbass]

If a full outer join isn't available to you, you can create a command like this:

select 'table1' as whichtable, table1.record, table2.record
from table1
left outer join table2 on
table1.record=table2.record
union all
select 'table2', table1.record, table2.record
from table2
left outer join table1 on
table2.record=table1.record

This would return all desired records, and you could use {command.whichtable} to identify which records were coming from which table.

-LB

 

[adubrovs]

My appologies but I was on the road and couldn't reply sooner.

I don't have full outer join option and the other suggestions did not seem to work.

Lbass - for some reason I don't get the "Add Command" option in the database explorer. I'm assuming that's what you ment.

I'm using Crystal 8.5.

 

[lbass]

8.5 is a very old version--time for an upgrade. There is still a way you could do this, but first can you clarify whether the fields you want to use in each table are the same? If they are not, please identify the field names, using the {table.field} format, and datatypes for each field in each table that you would want displayed on the report.

-LB

 

[adubrovs]

Yes, i'm trying to get an upgrade.

The field names are as follows: {portfolio.portfolio},{pf_security.market_pct} and {model.market_pct}.
The joint between portfolio and pf_security table is on {portfolio} and between pf_security and model table is on {security}.

So I want to be able to see all records regardless if a security is in the {model} table but not in the {portfolio} table or vice versa.
My report should like like this:

Portfolio Security Security% Model%
Port1. x 1.5% 1.5%
Port1. y 2.0%
Port1. z 1.0%

 

[lbass]

Are all records represented in the pf_security table?

In your sample data, you have not identified in the labels which table the fields are coming from. Please use the format {table.field}.

You also did not identify datatypes.

-LB

 

[adubrovs]

Yes, all records represented are in pf_security table. If that security is not in the model table i currently do not get a result and i can't use a left outer joint in this case since there are filters applied to the tables as well.

Portfolio - {portfolio.portfolio} - string
Security - {pf_security.security} - string
Security% - {pf_security.market_pct} - number
Model % - {model.market_pct} - number

Is that sufficient info?

 

[lbass]

Please paste a copy of the "show SQL query" as it currently appears into the thread.

-LB