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
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
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!!
-
-
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
-
-
and select same field where accn_num<>accn_num from the results
hope this helps
What is the problem that your query is throwing??
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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:
...
|
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.
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |