Apols if this is trivial ... but I'm a new user and
can't seem to figure it out.
I'm using QMF on TSO.
As an example assume I have two tables:
TABLE1 has 2 columns: PART_NUM and PRICE
TABLE2 has 2 columns: PART_NUM and STATUS

TABLE1 has thousands of entries, one for every possible PART_NUM.
TABLE2 has just a few entries, one for each of the small number of
parts that has some special status (like "SUPERCEDED", or "WITHDRAWN")

I want a report with three columns: PART_NUM, PRICE, STATUS and
one row for every PART_NUM in TABLE1.
For the PART_NUMs that do not exist in TABLE2 the report
can have a null or something in the STATUS col.

How do I do this ?

SELECT A.PART_NUM, A.PRICE, B.STATUS
FROM TABLE1 A, TABLE2 B
WHERE A.PART_NUM = B.PART_NUM
will give me a nice report for the subset of PART_NUMs which
exist in both tables.

I've played with a union like
SELECT A.PART_NUM, A.PRICE, B.STATUS
FROM TABLE1 A, TABLE2 B
WHERE A.PART_NUM = B.PART_NUM
UNION ALL
SELECT A.PART_NUM, A.PRICE, 'NONE'
FROM TABLE1 A
which gives me every part number okay, but gives me TWO
rows for the subset where there is an entry in each table.

This must be easy ... but I'm stuck!

SELECT
A.PART_NUM, A.PRICE.
COALESCE(B.STATUS,'NOT FOUND')
FROM
TABLE1 A
LEFT OUTER JOIN
TABLE2 B
ON A.PART_NUM = B.PART_NUM

Remember your Venn diagrams : a traditional (inner) join only includes the
rows which intersect. A Left Outer Join includes all rows from the left
(first mentioned) table and puts NULL in the right (second mentioned) table
column locations were there isn't a matching row.

I've used the COALESCE function to replace any NULL value with the string
"NOT FOUND". COALESCE() takes the first non-null value in the list of
columns and literals specified).

HTH

Phil Nelson
(teamdbaATWITHOUTSPAMscotdb.com)

Geoff wrote:

> Apols if this is trivial ... but I'm a new user and
> can't seem to figure it out.
> I'm using QMF on TSO.
> As an example assume I have two tables:
> TABLE1 has 2 columns: PART_NUM and PRICE
> TABLE2 has 2 columns: PART_NUM and STATUS
>
> TABLE1 has thousands of entries, one for every possible PART_NUM.
> TABLE2 has just a few entries, one for each of the small number of
> parts that has some special status (like "SUPERCEDED", or "WITHDRAWN")
>
> I want a report with three columns: PART_NUM, PRICE, STATUS and
> one row for every PART_NUM in TABLE1.
> For the PART_NUMs that do not exist in TABLE2 the report
> can have a null or something in the STATUS col.
>
> How do I do this ?
>
> SELECT A.PART_NUM, A.PRICE, B.STATUS
> FROM TABLE1 A, TABLE2 B
> WHERE A.PART_NUM = B.PART_NUM
> will give me a nice report for the subset of PART_NUMs which
> exist in both tables.
>
> I've played with a union like
> SELECT A.PART_NUM, A.PRICE, B.STATUS
> FROM TABLE1 A, TABLE2 B
> WHERE A.PART_NUM = B.PART_NUM
> UNION ALL
> SELECT A.PART_NUM, A.PRICE, 'NONE'
> FROM TABLE1 A
> which gives me every part number okay, but gives me TWO
> rows for the subset where there is an entry in each table.
>
> This must be easy ... but I'm stuck!

Geoff wrote:
> Apols if this is trivial ... but I'm a new user and
> can't seem to figure it out.
> I'm using QMF on TSO.
> As an example assume I have two tables:
> TABLE1 has 2 columns: PART_NUM and PRICE
> TABLE2 has 2 columns: PART_NUM and STATUS
>
> TABLE1 has thousands of entries, one for every possible PART_NUM.
> TABLE2 has just a few entries, one for each of the small number of
> parts that has some special status (like "SUPERCEDED", or "WITHDRAWN")
>
> I want a report with three columns: PART_NUM, PRICE, STATUS and
> one row for every PART_NUM in TABLE1.
> For the PART_NUMs that do not exist in TABLE2 the report
> can have a null or something in the STATUS col.
>
> How do I do this ?
>
> SELECT A.PART_NUM, A.PRICE, B.STATUS
> FROM TABLE1 A, TABLE2 B
> WHERE A.PART_NUM = B.PART_NUM
> will give me a nice report for the subset of PART_NUMs which
> exist in both tables.
>
> I've played with a union like
> SELECT A.PART_NUM, A.PRICE, B.STATUS
> FROM TABLE1 A, TABLE2 B
> WHERE A.PART_NUM = B.PART_NUM
> UNION ALL
> SELECT A.PART_NUM, A.PRICE, 'NONE'
> FROM TABLE1 A
> which gives me every part number okay, but gives me TWO
> rows for the subset where there is an entry in each table.
SELECT A.PART_NUM, A.PRICE, B.STATUS
FROM TABLE1 A
LEFT OUTER JOIN TABLE2 B
ON A.PART_NUM = B.PART_NUM

outer joins return a null row when no match is found.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab