sql query
There are 2 tables POLICY and NAME. The relationship between Policy and name is only the policy number.
and ther relation ship is like many to one. I mean
several policy number 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.
Software/Hardware used:
Software/Hardware used:
ASKED:
November 18, 2005 11:36 AM
UPDATED:
November 18, 2005 1:42 PM
Answer Wiki:
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
---
Sheldon Linker
Linker Systems, Inc.
www.linkersystems.com
sol@linker.com
800-314-1175
+1-949-552-1904 from outside of North America
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
