Two tables/one common field/no records returned

mspink
mspink used Ask the Experts™
on
I want to pull data from two tables into a Crystal Report.  They share one field: EmplName.  Currently, they are left outer joined by that field (inner join returns no records at all).  

When I run the report, I get none of the records from the second table.  From the first table, the fields that are numbers populate the report, but not fields of other types, including EmplName.  

We have many reports that link more than one table that work correctly, and I am not seeing what is different about this one?  Any ideas?    

 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
can you provide the sql syntaxt.  Crystal has a way to generate it.  Also do you have a where clause or a filter.  It seems extremly odd that records would return when using a left outer join and not for an inner join unless your where clause is looking for a null value in joined table or some sort of invalid data.

Author

Commented:
S:\Blswin32\Source\Reports\Replace\Reportdb.mdb
 SELECT `AttendSummRpt1`.`Rate1`, `AttendSummRpt1`.`Rate2`, `AttendSummRpt1`.`Rate3`, `AttendSummRpt1`.`Rate4`, `AttendSummRpt1`.`EmplName`, `AttendSummRpt1`.`Group`, `AttendSummRpt1`.`TotAttendHrs`
 FROM   `AttendSummRpt1` `AttendSummRpt1`
 ORDER BY `AttendSummRpt1`.`Group`
 EXTERNAL JOIN AttendSummRpt1.EmplName={?SERVER: EmplCode.EmplName}


SERVER
 SELECT "EmplCode"."User_Currency1", "EmplCode"."User_Currency2", "EmplCode"."User_Number1", "EmplCode"."User_Number2", "EmplCode"."User_Number3", "EmplCode"."User_Number4", "EmplCode"."EmplName"
 FROM   "OAMFGSQLNEW1"."dbo"."EmplCode" "EmplCode"
 WHERE  "EmplCode"."EmplName"={?S:\Blswin32\Source\Reports\Replace\Reportdb.mdb: AttendSummRpt1.EmplName}


I have to disagree with you, jetskij16.  A left outer join is more likely to return records than a inner join because the left join doesn't have to match records like the inner join does.  If the query returns data with a left join and no data is returned from an inner join that means that whatever column(s) you are joining on in the first table does not have a matching value in the second table.

Greg

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What happens if you leave the WHERE cluse off the SERVER SQL?

Are you using a COMMAND for the data or did you select tables?

mlmcc

Author

Commented:
I do not know how to edit the sql query directly.  I created the report by selecting the fields from tables selected with the database expert.  

Thanks for the replies.  
Sr. Business Intelligence Consultant/Architect
Commented:
1)  If an Inner Join doesn't return any records then that means there aren't any matches between the two tables.

2)  If you change to a Left Join, of course you'll get records from the Left table, but you won't get matches from the Right table. Since an Inner Join already failed  there aren't any matches, which is why nothing from the Right table was returned in this example.

3)  You're joining tables from two different data sources.  That's generally a bad idea from a performance perspective.  It also limits the types of joins you can create as well as other Crystal Reports functionality.  It might be better to include data from one table in a subreport (this is a big "if", since you normally don't want to do this at a detail record level).

4)  Since the two tables are in different data sources, there's a good chance they're not formatted the same.  What if, for example, one of the tables pads the field so there are always spaces at the end of the name?  What if one of the tables is case-sensitive and the other isn't?
Top Expert 2009
Commented:
I think your major issue here is that fact that EmplName is not returned on the left table properly.

Can you knock up a quick report on the AttendSummRpt1 table showing the EmplName and one or two of the other fields without including the other table at all? Does the data returned look correct?

Ditto for the other table?

Do a visual comparison of the two fields, and check their data types. Maybe one is in unicode and the other one isn't?

Author

Commented:
I appreciate the help.  The responses point to the fact that this is above what I can do...my knowledge of Crystal Reports is inadequate, obviously, and there are limits placed by our software vendor as to how you can alter the database (and still get support), so it seems that what I want to do cannot be done without paying the vendor a large sum for custom development, so we'll just struggle with the time-consuming process of combining the date from these two sources manually.
Maybe some of the previous posters could help on this as well but couldn't you do something like the following changes made in bold and those are sql 2008 syntax commands:


S:\Blswin32\Source\Reports\Replace\Reportdb.mdb
 SELECT `AttendSummRpt1`.`Rate1`, `AttendSummRpt1`.`Rate2`, `AttendSummRpt1`.`Rate3`, `AttendSummRpt1`.`Rate4`, `AttendSummRpt1`.`EmplName`, `AttendSummRpt1`.`Group`, `AttendSummRpt1`.`TotAttendHrs`
 FROM   `AttendSummRpt1` `AttendSummRpt1`
 ORDER BY `AttendSummRpt1`.`Group`
 EXTERNAL JOIN RTrim(LTRIM(AttendSummRpt1.EmplName))={?SERVER: EmplCode.EmplName}


SERVER
 SELECT "EmplCode"."User_Currency1", "EmplCode"."User_Currency2", "EmplCode"."User_Number1", "EmplCode"."User_Number2", "EmplCode"."User_Number3", "EmplCode"."User_Number4", RTRIM(LTRIM("EmplCode"."EmplName")) AS EmplName
 FROM   "OAMFGSQLNEW1"."dbo"."EmplCode" "EmplCode"
 WHERE  "EmplCode"."EmplName"={?S:\Blswin32\Source\Reports\Replace\Reportdb.mdb: AttendSummRpt1.EmplName}

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial