Left Outer Join not giving results needed 1

[modglin]

Using Crystal 8.5. I have two tables that I am linking together by Employee # and Company Name.
Table1 has ALL employee information
Table2 has classes taken.
I have used a left out join from Table1 to Table2. My understanding is that when I use that type of join, I will get all records from Table1 and only the records that match from Table2.
Not so in this case. If I pull in all the information from Table1, I have 1,929 records. As soon as I pull in a field from the Table2 - it drops to 567 records.
I have tried reversing the link, equal join (which I knew would not give me the info), right outer and etc and the fields from the first table do not show back up.
The link is through ADA - which is from a FoxPro database.

 

[rhinok]

Can you please post your record selection Criteria and your SQL Query (Database|Show SQL Query)?

 

[modglin]

SELECT
hrpersnl.p_active,
hrpersnl.p_fname,
hrpersnl.p_level3,
hrpersnl.p_lname,
hrpersnl.p_sendate,
hrpersnl.p_termdate,
nempcert.certcode
FROM
hrpersnl hrpersnl LEFT OUTER JOIN nempcert nempcert ON
hrpersnl.p_company = nempcert.company AND
hrpersnl.p_empno = nempcert.empno
WHERE
hrpersnl.p_sendate >= {d '2003-09-16'} AND
hrpersnl.p_sendate <= {d '2003-10-15'}
ORDER BY
hrpersnl.p_level3 ASC

 

[rhinok]

The SQL query is pretty straightforward. Additional filtering could be occuring on the client side. Can you please post your Record Selection Criteria (Report|Edit Selection Formula|Record.../Group...)?

 

[modglin]

{hrpersnl.p_sendate} in DateTime (2003, 09, 16, 00, 00, 00) to DateTime (2003, 10, 15, 00, 00, 00)

The strange thing is, I ran a more complicated version of this report a few days ago and had an employee appear and when I did a refresh today, she disappeared from the list.
I thought it might have been some of the formulas that was causing the problem so I created this report that only has one formula and that is combining last and first name. Other than that, it is pretty straight forward, pulling fields from the two tables and putting a select on the date range on the hire date. When I created this report, I pulled in all the fields from the HRPERSNL table and had 42 rows returned. When I added one field from the NEMPCERT table, it went to 25 (the other day I had 26 - so it was not working entirely, but at least one more showed up). I checked to make sure that I had it set up with Left Outer. I even tried changing it to Right Outer, Equal and reversng links.

 

[paulmarr]

Hi Modglin,

Your problem would seem related to the type of connection you are using to access the database &quot;ADA&quot;?

I had a previous experience - I was using a native driver to connect to the database - and the same thing happened. Crystal passed the SQL including the Left Outer Join - I could see it in the SQL - but it didn't work either.

I swapped to an ODBC connection and bingo! My problem was the client didn't want to use ODBC - so in the end we created a subreport - not the most efficient way to go but solved the problem.

Hope this helps you!

Cheers,

paulmarr

 

[modglin]

THANKS!!!!!!!!!!!!
I created the report through the ODBC connection we used to use before the software upgrade and it returned more row - which is correct since some employees have more than one class taken. The funny thing is the software support group did not say anything when I told them that I was using the ADA connection like they have recommended. Will have to tell them the solution.
Appreciate your help.
Myra

 

[rhinok]

Good call paulmarr(*). I completely skipped over that in reading Myra's original post.