sql for Evette

gistfile1.sql

-- revised to use a sub-select and to report only those records with BOTH a
-- matching 856$3 and 856$9 -- this sub-select may not be the most efficient way
-- to do this. also eliminated tag/subfield/value from the output
SELECT au.usrname, bre.tcn_value, bre.id, bre.create_date, 
bre.edit_date
FROM metabib.real_full_rec as mrfr
LEFT JOIN biblio.record_entry as bre on bre.id = mrfr.record
LEFT JOIN actor.usr as au on au.id = bre.editor
WHERE bre.deleted = 'f'
AND mrfr.tag = '856'
AND 
(mrfr.subfield='3'
AND mrfr.value like '%excerpt%')
AND mrfr.record IN 
(SELECT record from metabib.real_full_rec WHERE tag='856' AND subfield='9' AND UPPER(value) LIKE '%NDL%')
ORDER BY au.usrname, bre.id;