How to Join Tables for a Report
Suppose you want to assemble a report from a database of the books in your personal library. You decide upon a report design to present title, author, and subject information like this:
TITLE OF BOOK
Author1 Subject1
Author2 Subject2
Subject3
In your database, you have designated three separate tables to hold this information--one for titles (title), one for authors (name) and one for subject information (subject):
Book Table Definition
Book Data for the Sample Report
Author Table Definition
Author Data for the Sample Report
Subject Table Definition
Subject Data for the Sample Report
Now you must combine these tables to produce the data shown in the following table:
The easiest method is to use a UNION clause, as is shown in the .query section of the following report example:
.NAME books1
.OUTPUT books1.out
.LONGREMARK
The BOOKS report demonstrates the use of joining tables
for producing a report.
.ENDREMARK
.QUERY
select b.id, b.title, a.name, '' as subject, 1 as code
from book b, author a
where b.id = a.id
union
select b.id, b.title, '' as name, s.subject, 2 as code
from book b, subject s
where b.id = s.id
.SORT title, code
.BREAK title, code /* title and code will be break columns */
.RIGHTMARGIN 80 /* Initialize right margin */
.DECLARE
authors_column = integer,
subject_column = integer,
title_string = varchar(8)
.HEADER report
/* Initialize variables */
.LET authors_column = 5
.LET subject_column = 20
.HEADER title
/* Reset margin and print title of book */
/* start a block after printing the master info */
.LEFTMARGIN 0
.ULCHARACTER '='
.UNDERLINE
.PRINT title .NEWLINE
.NOUNDERLINE
.BLOCK
.HEADER code
/* goto the top of the block each time code changes */
/* set margin to the correct column for the code type */
.TOP
.ULCHARACTER '-'
.IF code = 1 .THEN
.LEFTMARGIN $authors_column
.LET title_string = 'Authors'
.ELSEIF code = 2 .THEN
.LEFTMARGIN $subject_column
.LET title_string = 'Subject'
.ENDIF
.UNDERLINE
.PRINT $title_string .NEWLINE
.NOUNDERLINE
.DETAIL
/* test the value of code to see which column to print */
.IF code = 1 .THEN
.PRINT name .NEWLINE
.ELSEIF code = 2 .THEN
.PRINT subject .NEWLINE
.ENDIF
.FOOTER title
.ENDBLOCK /* end the block at the end of the master info */
.NEWLINE
Completed Report
Computer Programming and Arch.
======== =========== === ====
Authors Subjects
------- --------
Eckhouse architecture
Levy assembler
Computer
programming
QUEL User Notes for a Join
In QUEL, you specify a join of several tables with a retrieve statement. In QUEL, you would use these queries to join the tables:
destroy tempbooksq\p\g
range of b is book\p\g
\range of a is author\p\g
range of s is subject\p\g
create tempbooksq (
id = i4,
title = varchar(30),
name = varchar(15) not null with default,
subject = varchar(15) not null with default,
code = i1
)\p\g
append tempbooksq (b.all, a.name, code=1)
where b.id = a.id\p\g
append tempbooksq (b.all, s.subject, code=2)
where b.id = s.id\p\g