How to identify identical master-detail records.

David Mujica

Without going into gruesome detail, I would like to get some ideas on how I might approach this problem. Given that I have a classic master-detail relationship in my database, lets call the tables Master and Detail. There is a master ID, and a detail ID for every row and every detail record has a reference to the master ID which it belongs. If master record A has 3 detail lines (Part 1, Part 2, Part 3) and master record B also has those same detail records (Part 1, Part 2, Part 3) then I want to indicate that master record A and B are identical. Can anyone suggest a method to do this ? My initial idea is to use 2 SQL cursors where I would be looping through the data with Cursor1 from top to bottom, then use Cursor2 to scan the same data for each occurence of Cursor1. Jeeze, sounds kind of lame. X| There must be a better way. Any suggestions would be greatly appreciated.

Richard Deeming

Something along these lines might get you started:

SELECT
A.ID As OriginalID,
B.ID As DuplicateID
FROM
-- Every pair of master records:
MasterTable As A
CROSS JOIN
MasterTable As B
WHERE
-- Only those pairs where A is earlier than B:
A.ID < B.ID
And
-- All detail lines for A exist for B:
Not Exists
(
SELECT
(relevant columns)
FROM
DetailTable
WHERE
MasterID = A.ID

  EXCEPT
  
  SELECT
     (relevant columns)
  FROM
     DetailTable
  WHERE
     MasterID = B.ID

)
And
-- All detail lines for B exist for A:
Not Exists
(
SELECT
(relevant columns)
FROM
DetailTable
WHERE
MasterID = B.ID

  EXCEPT
  
  SELECT
     (relevant columns)
  FROM
     DetailTable
  WHERE
     MasterID = A.ID

)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Lost User

You can use the GROUP BY clause on the Detail table with Part1, 2 and 3 in the group clause and take the distinct DetailID and MasterID from the table. But Group By requires an aggregate function and I'm not sure how to take distinct values using it. If you are sure you need only 2 matching rows, you can use MIN and MAX functions of course.

GuyThiebaut

You could use something like the except or minus(depending on your flavour of sql) clause to find differences between queries for rows referencing the same master ID. This would return differences, those rows not returned would be equal.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

David Mujica

Wow, that looks very interesting. I will see if I can get it to work.

Mycroft Holmes

Normal way to acknowledge thanks is to up vote the answer - a message is appreciated as well.

Never underestimate the power of human stupidity RAH

Richard Deeming

An up-vote before you know whether the answer works might be a bit premature.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer