SQL - Finding all child records for a given record

Using the keys of the parent find all the child records:


-- Tables that are associated with a publication.
SELECT DISTINCT RECNAME
FROM sysadm.PSRECDEFN A
WHERE RECNAME LIKE 'GUR%'
AND RECTYPE = 0
-- Key 1.
AND EXISTS
(SELECT 'X'
FROM sysadm.PSKEYDEFN B
WHERE B.RECNAME = A.RECNAME
AND B.FIELDNAME = 'GUR_PBLCTN_REF_NR')
-- Key 2.
AND EXISTS
(SELECT 'X'
FROM sysadm.PSKEYDEFN B
WHERE B.RECNAME = A.RECNAME
AND B.FIELDNAME = 'EFFDT')