# 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`

`Employee` | `Day` | `Job no.` | `Hours` | `Job Description`
`Jones` |
    Mon Feb 28
    Tue Mar 1
    Wed Mar 2
    Thu Mar 3
`Jordan` |
    Mon Feb 28
    Tue Mar 1
    Wed Mar 2
    Thu Mar 3
`Kilroy` |
    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
`Mosca` |
    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
`Peterson` |
    Mon Feb 28
    Tue Mar 1
    Wed Mar 2
    Thu Mar 3
`Scarlatti` |
    Mon Feb 28
    Tue Mar 1
    Wed Mar 2
    Thu Mar 3
`Wladislaw` |
    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