473,326 Members | 2,168 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

selecting duplicate record with one different field

35
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_NUM
HAVING COUNT (*) >1
Feb 6 '08 #1
5 2341
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_NUM
HAVING COUNT (*) >1
SEND IT PROOER WAY
CREATION THREE TABLES CODE YNTAX
AND YOUR QUERY AND WHAT ERROR YOU ARE GETTING
Feb 7 '08 #2
amitpatel66
2,367 Expert 2GB
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_NUM
HAVING COUNT (*) >1
Could you please explain in detail about your requirement rather in clear manner so that our experts could help you out!!
Feb 7 '08 #3
kwartz
35
Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> desc matchedstudies
  3.  Name                                      Null?    Type
  4.  ----------------------------------------- -------- --------------------------
  5.  STD_IUID                                  NOT NULL VARCHAR2(64)
  6.  MS_RD_RID                                 NOT NULL NUMBER(9)
  7.  
  8.  
  9. SQL> desc queue
  10.  Name                                      Null?    Type
  11.  ----------------------------------------- -------- -------------
  12.  RQ_RID                                    NOT NULL NUMBER(9)
  13.  ID                                                 VARCHAR2(64)
  14.  STUDY_DESC                                         VARCHAR2(64)
  15.  STD_DATE                                           DATE
  16.  IUID                                      NOT NULL VARCHAR2(64)
  17.  PAT_ID                                    NOT NULL VARCHAR2(64)
  18.  ACCN_NUM                                           VARCHAR2(16)
  19.  
  20.  
  21. SQL> desc ris
  22.  Name                                      Null?    Type
  23.  ----------------------------------------- -------- ---------------------------
  24.  RD_RID                                    NOT NULL NUMBER(9)
  25.  PAT_NAME                                           VARCHAR2(64)
  26.  PAT_SEX                                            VARCHAR2(16)
  27.  ACCN_NUM                                           VARCHAR2(16)
  28.  STD_DESC                                           VARCHAR2(64)
  29.  STD_DATE                                           DATE
  30.  EXAM_CODE                                          VARCHAR2(64)
  31.  STD_IUID                                           VARCHAR2(64)
  32.  
  33.  
  34.  
  35.  
  36. select m.ms_rd_rid, m.std_iuid, count (*)
  37.  from matchedstudies m, ris r, queue q
  38.  where m.std_iuid = q.iuid,and m.ms_rd_rid = r.rd_rid 
  39.  having count (*) > 1
  40. group by q.pat_id, q.id m.std_iuid, r.rd_rid
  41.  
  42.  
and select same field where accn_num<>accn_num from the results


hope this helps
Feb 7 '08 #4
amitpatel66
2,367 Expert 2GB
What is the problem that your query is throwing??
Feb 8 '08 #5
kwartz
35
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.
Feb 8 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: John | last post by:
Hello, I have a table called BUILDREQUESTS which I want to select from, depending on the project ID of each record. The Project ID field is indexed. (A) This query runs almost instantly: ...
4
by: Doslil | last post by:
I have a form which has a subform.In the main form I have only one field (this is a drop down list and has a query attached to it) which selects empno,Name from the EmployeeInformation table. ...
1
by: Robert | last post by:
How can I query an existing table and update a field in each record in the table with the occurrence count of each record e.g. update field to 1 (= first record occurrence), update field to 2 for...
8
by: Mark | last post by:
When my form goes to a new record, I have a procedure that copies the last record added to the form's underlying table into the form. The intent is that a series of new records may have the same...
2
by: Seth | last post by:
I have a query with field names and . When I access any given record in form "test", I want the user to be alerted if there is more than one record with the same first name and last name. This...
6
by: aaj | last post by:
Hi all I use a data adapter to read numerous tables in to a dataset. The dataset holds tables which in turn holds full details of the records i.e. keys, extra colums etc.. In some cases I...
8
by: Iona | last post by:
Hi Allan, I'm using a nifty piece of code you put on here some time back to do a duplicate entry check as below. I'm using to check for duplicate names. However I am getting an error message on...
9
by: Tom_F | last post by:
To comp.databases.ms-access -- I just discovered, to my more than mild dismay, that some tables in my Microsoft Access 2003 database have duplicate numbers in the "AutoNumber" field. (Field...
4
by: Thomas Arthur Seidel | last post by:
Hello to all, I have a small or big problem with a customer data base, where during a change of system we might have created duplicate records. This should be easy to find, you might think, but,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.