Link to home
Start Free TrialLog in
Avatar of Jerry Seinfield
Jerry SeinfieldFlag for United States of America

asked on

Crystal Reports 2013 - outer join - missing rows that don't match

Hello there,

I've created a report base on 4 tables, every row is unique on each table. So, I need to place all fields from all tables in a across tab table base on a group. Base on this group field, they are some rows that don't match in all tables.

I tried and outer join but option is disable. Also, I can only use left outer join, so I duplicated 3 of the tables, and I did link them left and right to the other table, in order to get all records, but is not working. When I looked at the tables in "field explorer" all data that I need is there, but when I added the fields to the report, it disappears and I got a blank result set.

Crystal doesn't support outer join? ah, the database is in MySql v 6.3.8

I hope someone could help me...thanks in advance.
Avatar of Jerry Seinfield
Jerry Seinfield
Flag of United States of America image

ASKER

Let me add this....these are the tables:
today table      
state      sales
AZ      1000
FL      2000
NY      3000
      
lastweek table      
state      sales
AZ      1001
CA      2001
FL      3001
      
lastmonth table      
state      sales
AZ      1002
NY      2002
FL      3002
      
last year table      
state      sales
AZ      1003
CA      2003
FL      3003

This is I got this REPORT:      
      
state      sum of sales
AZ      4006
FL      11006
      
But I want:      
      
state      sum of sales
AZ      4006
FL      11006
CA      4004
NY      5001
SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mike McCracken
Mike McCracken

IS this basically what you want?

mlmcc
SalesReport.rpt
Sales-Data.xls
Thanks guys, I think I'll work in idea with more UNIONs.

Maybe I over simplified my problem. Let me explain:

My "tables" are not really tables, in fact I built every one of them with a complicated query than already have many UNIONs. And my report is a complicated dashboard that is working beautifully, except for the fact that is not sowing a state X in "lastyear table" that is not in Current table AND also is not showing a state Y in "today table" that is not in "lastyear table". I used LEFT Outer join but Crystal is putting this as a Inner join. My dashboard has missing states X and Y. What I need desperately in order of not loosing all my work is an OUTER JOIN that Crystal is not supporting.

Any ideas?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, that's the idea...thanks, mlmcc

I've created a states table with select distinct and union all the rest of the tables.
Now, these tables are left outer join sates table.
Excellent, now I have all states on the report because I'm using for grouping states.state

Thanks, I appreciate it.
Thanks for all support....Have a great day!!!