Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    8

    Unanswered: Duplicate information in parent and child tables

    I have a parent table and a child table. In order to detect duplicate information I need to consider both tables. This means that a "duplicate record" consists of one parent record and one or more child records.
    Is there any standard approach to solve this issue?

    Respectfully,
    Jorge Maldonado

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    assume that the columns of the parent table are p1, p2, p3

    assume that the columns of the child table are c1, c2, c3, c4

    then you would find duplicates like this --
    Code:
    SELECT p1,p2,p3,c1,c2,c3,c4
      FROM parent
    INNER
      JOIN child
        ON c2 = p1 -- assumed pk/fk relationship
    GROUP
        BY p1,p2,p3,c1,c2,c3,c4
    HAVING COUNT(*) > 1
    so to answer your question, yes, the standard approach to this issue is GROUP BY with HAVING COUNT(*) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    8
    Quote Originally Posted by r937 View Post
    assume that the columns of the parent table are p1, p2, p3

    assume that the columns of the child table are c1, c2, c3, c4

    then you would find duplicates like this --
    Code:
    SELECT p1,p2,p3,c1,c2,c3,c4
      FROM parent
    INNER
      JOIN child
        ON c2 = p1 -- assumed pk/fk relationship
    GROUP
        BY p1,p2,p3,c1,c2,c3,c4
    HAVING COUNT(*) > 1
    so to answer your question, yes, the standard approach to this issue is GROUP BY with HAVING COUNT(*) > 1
    I appreciate your response. I guess I missed to include additional information about my issue. Let's say that there is already one parent record and several child records inserted in their respective tables. Now, the user tries to insert new information which would duplicate those records already in the tables. I want to avoid such duplication so what I need is to detect it in advance. Suppose that the new information the user is trying to add is temporarily saved in variables so this means that p1, p2 and p3 are in variables vp1, vp2 and vp3; and c1, c2, c3 and c4 are in variables vc1, vc2, vc3 and vc4. What statements would I add to your solution?

    With respect,
    Jorge Maldonado

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by JORGEMAL View Post
    I guess I missed to include additional information about my issue.
    yup

    and i missed to include additional info in my answer

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •