sql query
Home > IT Answers > Oracle > sql query
deyyam
0 pts.
0
Q:
sql query
Oracle, DB2, Database
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.
ASKED: Nov 18 2005  11:36 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
SheldonLinker
0 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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
Last Answered: Nov 18 2005  1:42 PM GMT by SheldonLinker   0 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



0