looking for value based on a group of rows

[fthomas1973]

I have a situation where there are multiple rows for one value (an insurance policy) that represent subfolders. I need a list of policies that do not have a certain subfolder.

SELECT APP_FOLDER_TAB.AF_TITLE, CABINET_FOLDER_REL.CF_FOLDER_ID,CONTAINER_TAB.CT_TITLE_TXT
FROM APP_FOLDER_TAB INNER JOIN CABINET_FOLDER_REL ON APP_FOLDER_TAB.AF_FOLDERID = CABINET_FOLDER_REL.CF_CABINET_ID
JOIN CONTAINER_TAB ON CONTAINER_TAB.CT_ID = CABINET_FOLDER_REL.CF_FOLDER_ID
ORDER BY AF_TITLE

RESULTS
AR00000037 87763060261 Acct/DB Correspondence
AR00000037 10425806099 Application
AR00000037 10425806100 Dec Pages / Invoices / Worksheets
AR00000037 10425806101 System Generated Letters
AR00000037 10425806102 UW Correspondence
AR00000037 10425806103 Loss Information
AR00000037 10425806104 Change Requests
AR00000037 10425806105 Inspections
AR00000037 10425806106 Reports
AR00000040 44160405690 Acct/DB Correspondence
AR00000040 70096581474 Application
AR00000040 70096581475 Dec Pages / Invoices / Worksheets
AR00000040 70096581476 System Generated Letters
AR00000040 70096581477 UW Correspondence
AR00000040 70096581478 Loss Information
AR00000040 70096581479 Change Requests
AR00000040 70096581480 Inspections
AR00000040 70096581481 Reports

I would like a result of the AF_TITLE that does not have group of rows that contains Acct/DB Correspondence.

 

[johnv20]

a quick & dirty method

select AF_TITLE
from APP_FOLDER_TAB
where AF_TITLE not in
	(
	select AF_TITLE
	from APP_FOLDER_TAB a, CABINET_FOLDER_REL b, CONTAINER_TAB c
	where a.AF_FOLDERID = b.CF_CABINET_ID
	and b.CF_FOLDER_ID = c.CT_ID
	and c.CONTAINER_TAB = 'Acct/DB Correspondence' 
	)

 

[fthomas1973]

works great!

Thanks