Actian X 11.1 | How to Join Tables for a Report

Character-based Querying and Reporting Tools User Guide > Character-based Querying and Reporting Tools User Guide > F. Report-Writer Report Examples > Report Creation Using Several Tables > How to Join Tables for a Report

Was this helpful?

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

Last modified date: 11/09/2022