Thanks, Daniel (and Dore). That helps. But let me now ask the bigger question <g>. I'll use the notorious student and class analogy <g>....

I have these tables and fields:
Students.dbf: studentID, name
Enrolled.dbf: studentID, classID
Classes.dbf: classID, roomID, className
Rooms.dbf: roomID, buildingID
Building.dbf: buildingID name

I want a list of ID numbers for students and a count of classes that each student has in Building #4. Note that I especially want the list to include the ID numbers of students who have NO classes in Building #4.

Now, I know that I can get a list of students who do attend classes in Building #4 like this:

SELECT Enrolled.studentid, COUNT(*) AS cnt;
FROM Enrolled, Classes, Rooms;
WHERE Enrolled.classID=Classes.classID AND ;
Classes.roomID=Rooms.roomID AND ;
Rooms.buildingID=4 ;
GROUP BY 1;
INTO CURSOR temp

And then (with the help of Dore's and Daniel's earlier messages) I can add back the IDs of all of the other students (with cnt=0):

SELECT Students.studentID, temp.cnt;
FROM Students ;
LEFT JOIN temp ON Students.studentID=temp.studentID

Or I suppose that I could have selected the students with zero (FROM Students WHERE NOT IN TEMP) and UNIONed it back with TEMP.

But my main concern is processing speed. The key fields are all indexed but these tables could have 300k+ records in them and it could be running on a slow machine. So I want to avoid two queries if there is a way to do it faster.

Can I somehow JOIN the Student table to the first query above so that all of the StudentIDs are represented? That is, can I produce this list with one query instead of two?

Thanks for any more help.

PS: How do you post code snippits so that the formatting is preserved? Should I just manually enter the PRE and /PRE codes?

-Bob