Retrieve fields in one table that do not appear in another 1

[Deano1976]

Crystal Reports Version 11

I have a table which contains Training Course names (let's call it Table 1) and another table that contains Employees and the courses they have attended (let's call it Table 2).
I want to be able to list all the Training Courses (Table 1)followed by a list of Employees (Table 2) who have not attended each course.
I've tried various join types, but cannot get it to work.

 

[IanWaterman]

YOu must use a left outer join from T1 to T2.

If you have any select filters on T2 it will over ride the LO join.

So if you have
t2.field = "X"

change to

(isnull(t2.field) or t2.field = "X")

Ian

 

[Madawc]

A crosstab of employees and courses would show the gaps, but it would show everything.

The only other solution I can see is to do a 'mock-crosstab', but only showing lines when the total is zero.

Or you could do it in SQL, if that's possible with your set-up.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Deano1976]

I can see what you are both saying, but I have additional issues on top of that.
For the linking part my employee table only contains those employees that have taken a course (essentially it is a training record table). I have a third table with all the companies employees in it. How do I get the links to work through all three of these tables with the result being as per my initial post?
Table 1 (All Courses with Course Ref as the link field)
Table 2 (Training Records with Course Ref and Employee Ref)
Table 3 (All Employees with Employee Ref as the link field)

For the cross-tab solution (regular and 'mocked') I can see how that would work, but I have over 3000 employees and more than 100 courses.

Any further ideas?


Apologies for the original double posting?

 

[lbass]

The following seems to work. First insert only the Courses table in a main report, and insert a group on {Courses.CourseRef}. Then insert a subreport and choose "add command" as your datasource. In the command, create a string parameter (assuming that CourseRef is a string) and name it:

pm-Training.CourseRef

...and then enter something like the following as the query, using the correct table field names and adapting the punctuation to your datasource:

Select Employees.`EmployeeRef`
From Employees
Where Employees.`EmployeeRef` not in
(
select A.`EmployeeRef`
from Training A
where A.`CourseRef` = '{?pm-Training.CourseRef}'
)

Say OK, and when prompted, enter a CourseRef value to allow the command to compile. Then go into the linking screen and move {Courses.CourseRef} to the right and make sure that {?pm-Training.CourseRef} appears in the lower left corner (the right hand side should be grayed out).

Place the subreport in the group footer of the main report and suppress the detail section.

-LB

 

[Deano1976]

Thanks for your reply, however I'm a little confused by the query you have written. I should have detailed my tables and fields more clearly when I first posted.

Could you interpret the tables and fields below into the query you wrote accordingly, please?

Table: COURSE with a field COURSE_REF. This table contains all the available courses.
e.g.
COURSE_REF
Advcust
Cust
Tel

Table: EMPLOYEE with field EMPLOY_REF. This table contains all employees in the company.
e.g.
EMPLOY_REF
00001
00002
00003
00004
00005

Table: TRAINREC with fields EMPLOY_REF and COURSE_REF. This table contains only employees that have attended any of the training courses and has a record for each course they would have attended (So one employee could have four records if they have attended 4 courses)
e.g.
EMPLOY_REF COURSE_REF
00001 Advcust
00002 Cust
00002 Tel
00004 Tel
00005 Advcust
00005 Cust
00005 Tel
(00003 Blank) Therefore no record would exist

 

[lbass]

This seems a pretty straightforward translation. I understand your fields. All you need to do is substitute your exact table and field names. You might need to change the punctuation. Take a look at how the query looks in database->show SQL query in your earlier report to see how punctuation is used.

If you are still having trouble, paste a SQL query into the thread so I can see the punctuation for your datasource, and also show me what you tried for the command and what error message you then received.

-LB

 

[Deano1976]

I've tried substituting the table names and fields, but when I am then prompted to enter a value to allow the command I get an error message.

This is add command...

?pm-TRAINREC.COURSE_REF

...and this is the code I have substituted...

Select "EMPLOYEE"."EMPLOY_REF"
From "ANC"."EMPLOYEE""EMPLOYEE"
Where "EMPLOYEE"."EMPLOY_REF" not in (
Select "TRAINREC"."EMPLOY_REF"
From "TRAINREC"
Where "TRAINREC"."COURSE_REF" = {?pm-TRAINREC.COURSE_REF})

...and this is the error message...

Failed to retrieve data from the database.
Details:HY000:[Oracle][ODBC][ORA]ORA-03001:unimplemented feature
[Database Vendor Code: 3001]



This is an example of an SQL query for my database...

SELECT "COURSE"."COURSE_REF"
FROM "ANC43"."COURSE" "COURSE"
ORDER BY "COURSE"."COURSE_REF"


Any ideas?

 

[lbass]

Why do you show a different owner "ANC" instead of "ANC43"--are they different for different tables? I'm assuming it should have been ANC43--correct if necessary. You should have a space between the table name and alias, also, and you didn't implement correctly in the subselect. It should look more like the following with an alias "A" and quotes enclosing the parameter:

Select "EMPLOYEE"."EMPLOY_REF"
From "ANC43"."EMPLOYEE" "EMPLOYEE"
Where "EMPLOYEE"."EMPLOY_REF" not in (
Select A."EMPLOY_REF"
From "TRAINREC" A
Where A."COURSE_REF" = '{?pm-TRAINREC.COURSE_REF}')

Can I assume that you created the parameter on the right-hand side of the command area before adding it into the query?

-LB

 

[Deano1976]

lbass,

I have not had time to fully test this, but so far so good.

Thanks very much for your help.