Ignore duplicate records in select statement
Posted on 2009-04-15
I have 2 tables - Couples and Members. Each coupel record WILL always have 2 Member records.
I want to extract select fields form the 2 rtables, producing a combined record per couple.
The following statement (see snippet) select C.C_Id, C.C_Surname, M.M_Id, M.M_Couples_Link, M.M_Surname, M.M_First_Name,
S.M_Id, S.M_Couples_Link, S.M_Surname, S.M_First_Name
Couples C left join members M on C.C_Id = M.M_Couples_Link
left join Members S on C.C_Id = S.M_Couples_Link
(S.M_Id <> M.M_Id)
extracts the necessary data but creates two records per Couple record.
What do I need to be doing?