SQL query to find out policy numbers

pts.
Tags:
Database
DB2
Oracle
SQL
SQL Query
There are 2 tables POLICY and NAME. The relationship between POLICY and NAME is only the policy number. and their relationship is like many to one. I mean several policy numbers can have same name in the name table with that many records. Now my question is to find out the policy numbers having the same name. I want to find out this using a single SQL query not using a PL/SQL code. Thanks for you help.

Answer Wiki

Thanks. We'll let you know when a new response is added.

I’m assuming you have a situation like this:

Policy:
PolicyNumber CHAR(...),
NameId INTEGER,
...
Name:
NameId INTEGER,
Name VARCHAR(...),
...
Use this query:
WITH matches AS(
SELECT nameId,
COUNT(*)
FROM policy
GROUP BY nameId
HAVING COUNT(*)>1)
SELECT n.name,
p.policyNumber
FROM matches m,
name n,
policy p
WHERE m.nameId=n.nameId AND
m.nameId=p.nameId

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Deyyam
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/sql-query/ (0) Comments Read [...]
    0 pointsBadges:
    report
  • msi77
    Assuming POLICY (policy_num (PK))
    NAME (policy_num (FK), name)

    select policy_num
    from NAME where name in
    (
    select name 
    from NAME
    group by name
    having count(*) > 1
    )
    1,660 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following