The following query gives me a list of the policies and editions that I want to get name data for:
FROM NAME_XREF WHERE B25_NAMETYPE = 'MT'
GROUP BY POLICY,EDITION
HAVING COUNT(*) > 1
I need to somehow combine this with the name table joining on the namenum field to show the name,policy, and edition for each policy that has more than one occurence of the nametype 'MT' (those returned in the previous query)
This is what I have attempted, but it doesn't work:
FROM NAME_XREF X, NAME_ADDRESS N
WHERE X.NAMENUM = N.NAMENUM AND X.POLICY,X.EDITION IN(
FROM NAME_XREF A WHERE A.NAMETYPE = 'MT'
GROUP BY A.POLICY,A.EDITION
HAVING COUNT(*) > 1)
Database is oracle, any help would be greatly appreciated