Compare Rows in a Database

B

Bev

I have 500K records in a table. I want to extract only the records where
field7 in one row = field7 in another row AND where field8 in one row =
field8 in another row.

I have tried linking the table to itself, but this isn't working.

Any suggestions?
 
G

Gina Whipp

Bev,

You can use a self join on the table. Try this...

Query, create new and copy/paste in the SQL view

SELECT Table1.field7, Table2.field7
FROM YourTableName AS Table1 INNER JOIN YourTableName AS Table2 ON
Table1.field7 = Table2.field7;

You only need to fill in the Name of your Table and field names if they
differ from what you provided. The one showing is for field7 change to do
for field 8.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
J

John W. Vinson

I have 500K records in a table. I want to extract only the records where
field7 in one row = field7 in another row AND where field8 in one row =
field8 in another row.

I have tried linking the table to itself, but this isn't working.

Any suggestions?
Click to expand...

SELECT a.*, b.*
FROM tablename AS A
INNER JOIN tablename AS B
ON A.field7 = B.field7
AND A.field8 = B.field8
AND a.primarykeyfield < b.primarykeyfield;

The last clause ensures that the record doesn't find itself in the join, and
requires that the table has a non-null, non-duplicated field (a primary key
would fit that bill).
 
G

Gina Whipp

John,

Question...

Wouldn't your way mean both fields field 7 and 8 would have to be the same
to return records? So that if field7 is the same but field8 isn't it
wouldn't return that record?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
J

John W. Vinson

John,

Question...

Wouldn't your way mean both fields field 7 and 8 would have to be the same
to return records? So that if field7 is the same but field8 isn't it
wouldn't return that record?
Click to expand...

You're correct, Gina - I may have misread Bev's post:

I have 500K records in a table. I want to extract only the records where
field7 in one row = field7 in another row AND where field8 in one row =
field8 in another row.


If she meant the Boolean algebra operator AND it implies that both Field7 and
Field8 must match; if she's using the English language conjunctive meaning of
AND then a UNION of two separate queries (as you suggest) would be needed.

Bev?
 
B

Bev

John W. Vinson said:
SELECT a.*, b.*
FROM tablename AS A
INNER JOIN tablename AS B
ON A.field7 = B.field7
AND A.field8 = B.field8
AND a.primarykeyfield < b.primarykeyfield;

The last clause ensures that the record doesn't find itself in the join, and
requires that the table has a non-null, non-duplicated field (a primary key
would fit that bill).
Click to expand...
 
J

John Spencer (MVP)

And you could always use the find duplicates query wizard to build a query
that would do this

SELECT *
FROM TableName
WHERE Field7 In
(SELECT Field7
FROM TableName as Temp
GROUP BY Field7, Field8
HAVING Count(*) > 1
AND Temp.Field8 = TableName.Field8)
ORDER BY Field7, Field8

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Top