Outer Join / Null Values

[IanBruk]

I am using Crystal Reports Version 8.5 with an ODBC connection to a MySQL database.

My report has a Primary table and numerous One to One links to secondary tables.

I wish the report to generate values when one or both of the secondary tables contain no corresponding records. That is some Primary records have no corresponding record in the secondary table.

I can initiate "Left Outer Joins" from the primary to the secondary tables as long as I don't try to use a field from the secondary tables in the report. But if there is a field in the report from either one of the secondary tables Crystal Reports does not allow the "Left Outer Join" to be initiated and I then get an ODBC error message and the report becomes a blank page.

I tried working with the "IsNull" command. However, while the command works to allow the display of the field of the null records in the secondary table it does not allow the display of the field when the value is not null. It seems to offer one or the other. All fields with the null value or all fields with the not null value.

Help greatly appreciated. I have tried the knowledge bases and forums.

 

[MalcolmW]

I suspect that from what you describe that the ODBC driver that you are using for MySQL does not support left outer joins, either at all, or at least in the format you are using. At the same time, it is very unusual for a SQL database not to support left outer joins, as this is a part of the SQL 92 basic standard. You can simulate an outer join using inner joins and a union, but this is complicated. Are you sure you have your outer join syntax correct? Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[kenhamady]

When you aren't using a field from the secondary table Crystal doesn't even bother to create the join. You can confirm this by looking at the SQL that is generated. It will ignore the arrow that you draw, because you haven't requested anything from that table.

Can you do a Left outer join with just the primary and one of the secondary?
Will it return data? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[IanBruk]

I just retested after reading the responses. I can initiate one outer join and generate the correct result set. It will not however allow a second outer join. I checked the syntax by running the SQL statement in MySQL and it works.

 

[kenhamady]

So the SQL generated by Crystal is fine when run in the database directly, but won't go through ODBC? Sounds like a limitation of the ODBC driver.

Can you create a veiw/stored procedure within the database and report off of that? Does MySQL support views or stored procedures? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[IanBruk]

These message boards help to teach one to be concise in their statements.

I have no problem running the SQL statement in the database when I am only using one Left Outer Join. Using 2 doesn't work in either Crystal Reports or in the MySql database.

I checked and MySQL does not support stored procedures or views at this time.

I think my Database and ODBC driver are affecting the output. I gather everyone else is having no trouble using multiple Left Outer Joins in a report. I appreciate the responses to my post and will keep digging.

 

[ziggs]

We just went from Oracle 7.3 to Oracle 8i and are having similar problems. With 7.3 I can create multiple left joins with a problem. However, with 8i I'm having a problem. If anyone is finding any solutions, I'm interested in them too. Thanks

 

[Ngolem]

Look at an earlier post called "Query by Insanity" it has an excellent response to use of outter Joins

Jim