comparing 2 recordsets to make a 3rd 1

[toon10]

Hi

I was wondering what the logic to use for comparing 2 recordsets.

I have a RSCourseAttendedByStaff recordset (which is a list of staff members who have done a specific training course)

My other recordset, RSAllStaff, is a full list of staff members.

I want to compare the two and find out any names from RSAllStaff who have not done the specified course (i.e. who are not present in the RSCourseAttendedByStaff).

I have started the code...

Private Sub cmdCourse_Click()
Dim SQLCourseAttendedByStaff, SQLAllStaff As String
Dim RSCourseAttendedByStaff As New Recordset
Dim RSAllStaff As New Recordset

List1.Clear

If cmbCourse.Text = "" Then
    MsgBox "Course Required", vbInformation, "Select from List"
    cmbCourse.SetFocus
    Exit Sub
Else

    Open_cn

    SQLCourseAttendedByStaff = "SELECT Name, Course From TrainingRecord WHERE (Course = '" & cmbCourse & "') ORDER BY Name"
    SQLAllStaff = "SELECT DISTINCT Name FROM TraininRecord ORDER BY Name"

    RSCourseAttendedByStaff.Open SQLCourseAttendedByStaff, cn, adOpenStatic, adLockOptimistic, adCmdText 'create the recordset
    RSAllStaff.Open SQLCourseAttendedByStaff, cn, adOpenStatic, adLockOptimistic, adCmdText 'create the recordset

    '... loop to compare results
    'List1.AddItem RSCourseAttendedByStaff.Fields("Name") 'If they are a staff member but not on the SQLCourseAttendedByStaff list then add to listbox
End If

Close_cn

End Sub

 

[MajP]

Any reason you would not just write a simple query to return the values and populate the listbox from the query?

 

[toon10]

If you can write a query that would achieve this, I'd be happy to see it.

 

[Andrzejek]

How about:

SQLCourseAttendedByStaff = "SELECT Name, Course From TrainingRecord WHERE [blue]NOT [/blue](Course = '" & cmbCourse & "') ORDER BY Name"

Have fun.

---- Andy

 

[toon10]

Thanks for the response.

I've tried this before posting and the problem is that this doesn't return a list of staff members who have not done training on this course. It produces a list of all courses that staff members have done (excluding the selected course)

For example, if Frank Spencer has done several courses but not the one I selected, he still appears in the list because he has done other courses if that makes sense?

 

[gmmastros]

Not tested:

SQLCourseAttendedByStaff ="Select A.Name From (Select Distinct Name From TrainingRecord) As A Left Join (Select Name From TrainingRecord Where Course = '" & cmdCourse & "') As B On A.Name = B.Name Where B.Name Is NULL"

This should work well if your database is Microsoft SQL Server. If you are using another type of database, you may need to tweak the syntax a bit. Also, this is probably not the only way to write the query, but as is... it should work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[toon10]

Thank you very much. That looks great!

 

[PWise]

George:

Would your query return a name who has not taken any Course?

Try

Select [Name]
from Staff
left join(Select [Name] 
          From TrainingRecord 
          Where Course = '" & cmdCourse & "'
          )TraininRecord 
on Staff.[name]=TraininRecord.[name]
Where  TraininRecord.[name] is null

 

[gmmastros]

pwise,

You are correct, it would not return staff members that have not taken any course. I don't see any reference to a staff table in the original question, so I made the assumption that it does not exist. I merely combined the 2 original queries (using a derived table method) to return the data toon10 was looking for.

If there is a staff table, then your query is better than mine. However, there is another alternative that would perform even better than the derived table approach.

The following is SQL Server code. I use 2 derived tables to illustrate the code.

Declare @Staff Table(Name VarChar(50))

Insert Into @Staff(Name) Values('George Bush')
Insert Into @Staff(Name) Values('John McCain')
Insert Into @Staff(Name) Values('Barack Obama')

Declare @TrainingRecord Table(Name VarChar(50), Course VarChar(50))

Insert Into @TrainingRecord(Name, Course) Values('George Bush', 'Etiquette')
Insert Into @TrainingRecord(Name, Course) Values('Barack Obama', 'Etiquette')
Insert Into @TrainingRecord(Name, Course) Values('Barack Obama', 'Sensitivity')

Select * 
From   @Staff As Staff
       Left Join @TrainingRecord As TrainingRecord
         On  Staff.Name = TrainingRecord.Name
         And TrainingRecord.Course = 'Sensitivity'
Where  TrainingRecord.Name Is NULL

The key to this query is the placement of the course filter. Notice that I am left joining the 2 tables, but more importantly, I am filtering the course in the join condition, not the where clause. If I would have put the condition in the where clause (and remove the Is NULL check), this query would act like an inner join.

The important point to realize here is... When using a left join, you should NOT have any conditions in the where clause that filters data on the right side of a left join condition. Instead, move the condition to the join clause.

Another point to realize is that derived tables perform really well, but there is usually a slight advantage if you can write the query without it. SQL will do a better job of optimizing the execution plan.

To all: My apologies for making making this look like a SQL thread instead of a VB one.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom