Can you have left and right outer joins in Crystal reports 8.5

[Jonoiams]

Can you have a left and a right outer join in crystal reports 8.5?

I'm linking two tables and I want the report to show all the information in both tables is this possable in 8.5

Can anyone help me

Thanks

Jono

 

[dgillz]

Yes, ut you must use an ODBC data connection, not a native connection, to your data. What type of database are you using and how are you connecting to the database?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Jonoiams]

I'm using an Orical data base and a ODBC link

There are Left and right outer joins but I want to combine both of them so that I have all the information from both tables

 

[patge4]

Yes, CR does support outer/inner joins. Are you using the database expert on the menu? That's where you will find the feature that allows you do this.

 

[lbass]

You cannot use both a left and a right join between the same two tables. You could do a left join from table1 to Table2 in the main report and a left join from Table2 to Table1 in an unlinked subreport, but you would get some duplicate records which you would need to suppress using a shared variable.

A better solution might be to use a Union of the two tables in your SQL Query. I've never done this, but if you do a keyword search using "Union" you should be able to get enough help to do this.

-LB

 

[synapsevampire]

Assuming that these tables have the same columns, you can create a Union by creating the report with the first table, then copy the contents of the Database->Show SQL Query

Type the word Union in afterwards

Then paste the SQL again below it, and change the table names in the second SQL.

If they're not the same, please post example data and expected output.

-k

 

[Jonoiams]

I have one table of invoiced orders and table of keyed orders not yet invoiced.

I want to produce a report showing total orders.

Outer joins will only give total orders in one table and any matching orders in another table.

I want all orders

The tables have differant colums but do have colums that can be linked

Thanks for the help

 

[lbass]

Your description makes it sound like the two tables would have no records in common--invoiced and not invoiced. Then you could just use the unlinked subreport approach for the records from the second table.

Or does the Keyed Orders table include orders that have been invoiced? In that case, it doesn't seem likely that there would be records in the Invoiced Orders table that were not in the Keyed Orders table. In that case a left join from the Keyed Orders to the Invoiced Orders table would return all orders. As you can see, we need more information about your table structure.

Your last post does not explain whether you have tried previous suggestions--specifically, the Union method suggested by both SV and me would best address the situation where there were records in each table not found in the other, but with some records in common.

-LB

 

[Jonoiams]

Thanks for help
I tried the union as suggested but the tables are not the same.

What do you you mean by an unlinked subreport as I think that this will be the way to go for me.

If i have a unlinked subreport will I be able to get a total of all orders grouped by state

 

[synapsevampire]

You'd want to group the main report by state, place the subreport at the state level, and link the subreport by State.

This discovery process is taking too long, please post example data (from both data sources) and expected output.

-k