Re: SQL: Join file 1 and 2, but only if not in 3

[rob@xxxxxxxxx]

Joining files by a WHERE clause is something reserved for those who 
learned sql by running RTVQMQRY against their favorite query.  Start using 
JOIN.  Even when WHERE used to work.  You can go farther with it.

SELECT 
T02.ITNBR, T02.STID, T02.ITRV, T02.ITDSC, 
T02.UNMSR, T02.QCTYP, T02.QCDAY, T02.INTYP, T02.BLCF, T02.ALLOC
FROM WHDTA/SHRITMS T01, 
  JOIN AMFLIB1/ITMRVA T02 ON T01.ITNBR = T02.ITNBR
WHERE STID = 'H1P'
ORDER BY  T02.ITNBR ASC, T02.ITRV ASC 

Now expand this into something like

SELECT 
T02.ITNBR, T02.STID, T02.ITRV, T02.ITDSC, 
T02.UNMSR, T02.QCTYP, T02.QCDAY, T02.INTYP, T02.BLCF, T02.ALLOC
FROM WHDTA/SHRITMS T01, 
  JOIN AMFLIB1/ITMRVA T02 ON T01.ITNBR = T02.ITNBR
WHERE STID = 'H1P' and t02.itnbr not exists
  (select itnbr from itemhist)
ORDER BY  T02.ITNBR ASC, T02.ITRV ASC 

My first harping about the join was that I was thinking about an EXCEPTION 
JOIN but I am thinking the NOT EXISTS might be a better choice.

Rob Berendt