Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: select from two tables where count > 1 in one of them

    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

  2. #2
    Join Date
    Sep 2003
    Posts
    14
    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

  3. #3
    Join Date
    Sep 2003
    Posts
    2
    Sorry, yes the first query shouldn't have the b25_ attached. It is the same column.




    Originally posted by usha_nandini
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •