CHAPTER 4: Multiple SELECT Statements

The following script and report show how to do something which cannot be done by a single SELECT statement using a join. Consider the following: two tables have an attribute in common upon which they may be joined. The first table contains a list of all values for this attribute, that is, it contains a list of group members. The second table contains zero, one, or several records for each value of the attribute in the first table; that is, it may or may not contain several records for each group member. Suppose it is necessary to produce a report with an entry for every member of the group. Each entry should contain some data from the first table and any data for that group member from the second table.

This cannot be done using a simple join. If the two tables are joined, the resulting set of records will include only those items where the attribute value occurs in both tables; it will not include data from the first table for those members of the group which have no entries in the second table.

An example of this is a weekly report which lists all employees and shows the jobs they performed on each day of the week. Every employee should be listed on the report, and for each employee at least one entry is printed for each day of the week, even if that employee did nothing on that day.

This can be done with two SELECT statements. The first retrieves all the records from the personnel table, sorting them by name, and the second retrieves all the records from the hours table, sorting by date. Every name retrieved from the personnel table is printed; these records are from the first SELECT statement. Next, all the records from the hours table (the records from the second SELECT statement) are gone through. For each date the date is printed, and every personnel number checked against the current name in the personnel table. If the number is the same, the job description and hours are printed. A flag is set to avoid having an extra new line printed after dates on which the mechanics worked. The whole process of checking all the records from the second SELECT is then gone through for the next name from the first SELECT, and so on.

The report script and the report we want are shown below:

/* Breakdown of jobs per employee per day */

LET PAGEWIDTH = 80;
LET DATABASE = "repairs";
SELECT name, number FROM personnel SORT BY name;
	BECOMES CONTEXT one;
	SELECT FROM "hours" SORT BY date;
	BECOMES CONTEXT two;

HEADER
	NEWLINE 2;
	PRINT "SUMMARY OF JOBS/EMPLOYEE FOR EACH DAY"
		WIDTH pagewidth CENTER, NEWLINE 3;
	PRINT "Employee", col 15, "Day", COL 30,
		"Job no.",COL 40, "Hours", COL 50, "Job Description",
		NEWLINE;
	NEWLINE;
END;
LET lineflag = 0;
FOR EACH GROUP OF name IN CONTEXT one
	PRINT NAME, ":";
	LET empno = number;
	FOR EACH GROUP OF DATE IN CONTEXT two
		PRINT COL 15, DATE PIC "www aaa dd";
		FOR EACH RECORD
			IF (empno = number)
				PRINT COL 30, ATTR 'job no.',
					COL 40, ATTR "hours"
					WIDTH 5 CENTER, COL 50,
					description WIDTH 30,
					NEWLINE;
				LET lineflag = 1;
			END;
		END;
		IF (lineflag = 1)
			LET lineflag = 0;
		ELSE
			NEWLINE;
		END;
	END;
	NEWLINE;
END;

SUMMARY OF JOBS/EMPLOYEE FOR EACH DAY

Mon Feb 28
Tue Mar 1
Wed Mar 2
Thu Mar 3

Mon Feb 28
Tue Mar 1
Wed Mar 2
Thu Mar 3

Mon Feb 28
Tue Mar 1
Wed Mar 2

Thu Mar 3

352
352
352
352
352

2
2
6
8
2

Replace rear wheel
Replace headlights
Bent left door
Major body work
Replace windshield

Mon Feb 28
Tue Mar 1

Wed Mar 2
Thu Mar 3

350
352
352

410
410

2
2
3

3
2

Oil change
Adjust carburetor
Wheel alignment

Tune up 
Synch pistons

Mon Feb 28
Tue Mar 1
Wed Mar 2
Thu Mar 3

Mon Feb 28
Tue Mar 1
Wed Mar 2
Thu Mar 3

Mon Feb 28
Tue Mar 1
Wed Mar 2



Thu Mar 3

396
401
401
401
401

1
2
2
1
3

Lube & grease
Tire repair
Change tires
Sparks & tune
Wheel alignment