By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Find duplicate records on two fields

P: n/a
Hi,

Please excuse the newbie type question, but I am wracking my brains to
solve what should be a simple query.

I have three fields in a table F1, F2, F3. Any of the fields may
contain duplicate data, but I am only interested in where only the
first two fields match. I want to query and get only the records (all
three fields) where there are duplicates in the first two fields, e.g.

F1 F2 F3
1 2 3
1 2 4
1 2 4
2 3 4
2 3 4

Would return only, 1 2 3, 1 2 4 (because there are more than one row
with 1 2) and not any of the records with 2 3.

Hope this comes out clear.

Thanks in advance
Steve in UK

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"steevp" <st******@hotmail.com> wrote in
news:11*********************@g14g2000cwa.googlegro ups.com:
Hi,

Please excuse the newbie type question, but I am wracking my
brains to solve what should be a simple query.

I have three fields in a table F1, F2, F3. Any of the fields
may contain duplicate data, but I am only interested in where
only the first two fields match. I want to query and get only
the records (all three fields) where there are duplicates in
the first two fields, e.g.

F1 F2 F3
1 2 3
1 2 4
1 2 4
2 3 4
2 3 4

Would return only, 1 2 3, 1 2 4 (because there are more than
one row with 1 2) and not any of the records with 2 3.

Hope this comes out clear.

Thanks in advance
Steve in UK


In the query, put two instances of the table. It will show the
table names as TableName and TableName_1. Right click on each
name and set the alias property to A on one table and B on the
other.

Join A.F1 to B.F1, A.F2 to B.F2. Set the criteria for a.F3 <>
B.F3

You will still have a problem with your two instances of 1-2-4,
which you can resolve by adding a criteria on the primary key
(which you have not shown) being different.

--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #2

P: n/a
Thanks Bob!
Just the job. Adding a DISTINCT got rid of the other duplicates. The
code is as below for reference:

SELECT DISTINCT A.F1, A.F2, A.F3
FROM table1 AS A INNER JOIN table2 AS B ON (A.F1 = B.F2) AND (A.F2 =
B.F2)
WHERE (((A.F3)<>b.F3));

regards

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.