selecting duplicate record with one different field

[kwartz]

Can somebody please help me with a select statement for selecting duplicate records with one different field .

I have 3 tables: A, B and C

A has 2 fields
I ---- primary key
m


B
I ----foriegn Key reference I in A
N
ID
A_NUM
MB

C

N
ID
A_NUM
MB Primary key

I want to select
I, N, ID AND A_NUM
WHERE A.I=B.I AND B.MB = C.MB AND B.A_NUM<>C.A_NU M
HAVING COUNT (*) >1

[subashsavji]

Can somebody please help me with a select statement for selecting duplicate records with one different field .

I have 3 tables: A, B and C

A has 2 fields
I ---- primary key
m


B
I ----foriegn Key reference I in A
N
ID
A_NUM
MB

C

N
ID
A_NUM
MB Primary key

I want to select
I, N, ID AND A_NUM
WHERE A.I=B.I AND B.MB = C.MB AND B.A_NUM<>C.A_NU M
HAVING COUNT (*) >1

SEND IT PROOER WAY
CREATION THREE TABLES CODE YNTAX
AND YOUR QUERY AND WHAT ERROR YOU ARE GETTING

[amitpatel66]

Can somebody please help me with a select statement for selecting duplicate records with one different field .

I have 3 tables: A, B and C

A has 2 fields
I ---- primary key
m


B
I ----foriegn Key reference I in A
N
ID
A_NUM
MB

C

N
ID
A_NUM
MB Primary key

I want to select
I, N, ID AND A_NUM
WHERE A.I=B.I AND B.MB = C.MB AND B.A_NUM<>C.A_NU M
HAVING COUNT (*) >1

Could you please explain in detail about your requirement rather in clear manner so that our experts could help you out!!

[kwartz]

[code=oracle]

SQL> desc matchedstudies
Name Null? Type
----------------------------------------- -------- --------------------------
STD_IUID NOT NULL VARCHAR2(64)
MS_RD_RID NOT NULL NUMBER(9)


SQL> desc queue
Name Null? Type
----------------------------------------- -------- -------------
RQ_RID NOT NULL NUMBER(9)
ID VARCHAR2(64)
STUDY_DESC VARCHAR2(64)
STD_DATE DATE
IUID NOT NULL VARCHAR2(64)
PAT_ID NOT NULL VARCHAR2(64)
ACCN_NUM VARCHAR2(16)


SQL> desc ris
Name Null? Type
----------------------------------------- -------- ---------------------------
RD_RID NOT NULL NUMBER(9)
PAT_NAME VARCHAR2(64)
PAT_SEX VARCHAR2(16)
ACCN_NUM VARCHAR2(16)
STD_DESC VARCHAR2(64)
STD_DATE DATE
EXAM_CODE VARCHAR2(64)
STD_IUID VARCHAR2(64)




select m.ms_rd_rid, m.std_iuid, count (*)
from matchedstudies m, ris r, queue q
where m.std_iuid = q.iuid,and m.ms_rd_rid = r.rd_rid
having count (*) > 1
group by q.pat_id, q.id m.std_iuid, r.rd_rid

[/code]

and select same field where accn_num<>accn_ num from the results


hope this helps

[amitpatel66]

What is the problem that your query is throwing??

[kwartz]

It's not selecting any record.


Select ms_rd_rid, count (*)
from studies
having count (*) > 1
group by ms_rd_rid;


gets all duplicate record. Now I want records from this result that have different
accn_num.