Jerry Seinfield
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Thanks for all support....Have a great day!!!
ASKER
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