Two tables/one common field/no records returned

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?    

 
mspinkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jetskij16Commented:
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.
0
mspinkAuthor 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}


0
JestersGrindCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mlmccCommented:
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
0
mspinkAuthor 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.  
0
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PCIIainCommented:
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?
0
mspinkAuthor 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.
0
jetskij16Commented:
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}

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Networking

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.