Post reply

Many to Many Self Join?!

  • December 15, 2004 at 1:43 am

    #533902

    Think of it as a parent/child relationship if that isn't too Freudian.

    An employer is a parent the child is the employee.

    The customer is the child of contact 4.

    Your tbl_relationship table is correct but I would rename the fields parentId and childId.  This has no technical benefit but does add clarity.

    If you want to get all relationships for a particular contact then you do a simple union query

    SELECT 'Parent' AS Relationship , C.*

    FROM dbo.tblRelationship AS T INNER JOIN tblContact AS C

    ON T.ParentId = C.ContactId

    WHERE T.childId = @lContactId

    UNION ALL

    SELECT 'Child' AS Relationship , C.*

    FROM dbo.tblRelationship AS T INNER JOIN tblContact AS C

    ON T.ChildId = C.ContactId

    WHERE T.parentId = @lContactId

    If a pair of contacts can only have one relationship then the primary key will be parentId,ChildId but the key should be non-clustered.

    You need a second index on childId.

    LinkedIn Profile

Viewing post 1 (of 2 total)

You must be logged in to reply to this topic. Login to reply