I am trying to write a query that will combine a table (name) with another table (name_xref) where there are multiple records of a given nametype in the name_xref table.

table: name
fields: namenum, name

table: name_xref
fields: namenum, policy, edition, nametype

The following query gives me a list of the policies and editions that I want to get name data for:

SELECT POLICY,EDITION
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:

SELECT X.POLICY,X.EDITION,N.NAME
FROM NAME_XREF X, NAME_ADDRESS N
WHERE X.NAMENUM = N.NAMENUM AND X.POLICY,X.EDITION IN(
SELECT A.POLICY,A.EDITION
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

Hi,

One trivial question

In the first query you are using ' B25_NAMETYPE = 'MT' '
and in the second one you are using ' A.NAMETYPE = 'MT' '

Are you not referring to the same column in both the queries?

Usha

Sorry, yes the first query shouldn't have the b25_ attached. It is the same column.