F. Report-Writer Report Examples : Report Creation Using Several Tables : How to Join Tables for a Report
 
Share this page                  
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
Column Name
Type
Length
Nulls
Defaults
id
integer
4
yes
no
title
varchar
20
yes
no
Book Data for the Sample Report
id
title
1001
The C Programming Language
1002
Computer Programming Arch.
1003
The INGRES Papers
1004
Database Systems
1005
The Quiet American
Author Table Definition
Column Name
Type
Length
Nulls
Default
id
integer
4
yes
no
name
varchar
15
yes
no
Author Data for the Sample Report
Id
title
1001
Ritchie
1001
Kernighan
1002
Eckhouse
1002
Levy
1003
Stonebraker
1004
Ullman
1005
Greene
Subject Table Definition
Column Name
Type
Length
Nulls
Default
id
integer
4
yes
no
subject
varchar
15
yes
no
Subject Data for the Sample Report
id
Subject
1001
C
1001
Programming
1001
Language
1002
Architecture
1002
Assembler
1002
Computer
1002
Programming
1003
Database
1003
Ingres
1003
Computer
1004
Database
1004
Management
1005
Vietnam
Now you must combine these tables to produce the data shown in the following table:
id
title
name
subject
code
1001
The C Programming Language
Kernighan
1
1001
The C Programming Language
Ritchie
1
1001
The C Programming Language
 
C
2
1001
The C Programming Language
 
language
2
1001
The C Programming Language
 
programming
2
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