Bob,
Personally, I often prefer to split SQL SELECTs rather than creating a single but complex one. I find it much easier to read/maintain and quite often it performs much better. Specially if you know the data, you achieve the best performance if you can reduce the number of records with the first SELECT and use the first result set in the sub-sequent queries. While your example can be done with a single query (see TestB), in my tests it run much slower than the TestA. Your mileage my vary.

*-- Test A: 2 queries 
SELECT students.studentId,;
       students.name,;
       COUNT(*) AS cnt;
 FROM students;
  INNER JOIN Enrolled ON students.studentId=enrolled.studentId;
  INNER JOIN Classes ON enrolled.classId=classes.classId;
  INNER JOIN Rooms ON classes.roomId=rooms.roomId;
 WHERE rooms.buildingid="004";
 GROUP BY students.studentId;
INTO CURSOR step1

SELECT students.studentId,;
       students.name,;
       NVL(step1.cnt, 0) AS cnt;
 FROM students;
  LEFT JOIN step1 ON students.studentId=step1.studentId;
INTO CURSOR Test3A

*-- Test B: single query 
SELECT students.studentId,;
       students.name,;
       SUM(IIF(rooms.buildingId="004", 1, 0)) AS cnt;
 FROM students;
  LEFT JOIN Enrolled ON students.studentId=enrolled.studentId;
  LEFT JOIN Classes ON enrolled.classId=classes.classId;
  LEFT JOIN Rooms ON classes.roomId=rooms.roomId;
 GROUP BY students.studentId;
INTO CURSOR test3B

To format code you can use < PRE > and < /PRE > tags. In order to get the syntax color, I'm using a utility from Mike Helland that converts VFP code to HTML. It's available in the download setction.

HTH
>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