allanmark
asked on
Ignore duplicate records in select statement
Greetings all
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
from
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
where
(S.M_Id <> M.M_Id)
extracts the necessary data but creates two records per Couple record.
What do I need to be doing?
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
from
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
where
(S.M_Id <> M.M_Id)
extracts the necessary data but creates two records per Couple record.
What do I need to be doing?
ASKER
Ooops ... one of those days!!!
1 Bennett 10 1 Lewis Bev 9 1 Bennett Allan
1 Bennett 9 1 Bennett Allan 10 1 Lewis Bev
2 Thomas 12 2 Thomas Wendy 11 2 Thomas Dave
2 Thomas 11 2 Thomas Dave 12 2 Thomas Wendy
3 Jones 14 3 Jones Kelly 13 3 Jones Paul
3 Jones 13 3 Jones Paul 14 3 Jones Kelly
4 Brown 16 4 Brown Melanie 15 4 Brown Mark
4 Brown 15 4 Brown Mark 16 4 Brown Melanie
5 Van Der Merwe 18 5 V/D Merwe Lesley 17 5 V/D Merwe Sean
5 Van Der Merwe 17 5 V/D Merwe Sean 18 5 V/D Merwe Lesley
6 Peters 20 6 Peters Wendy 19 6 Peters Bill
6 Peters 19 6 Peters Bill 20 6 Peters Wendy
7 Smith 22 7 Smith Shirley 21 7 Smith Robert
7 Smith 21 7 Smith Robert 22 7 Smith Shirley
ASKER
Is this an acceptable method?
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, R.RowNo
from
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
inner join (SELECT ROW_NUMBER() OVER(ORDER BY M_Id) RowNo, M_Id FROM Members) R ON M.M_Id = R.M_Id
where
(S.M_Id <> M.M_Id) and
(R.RowNo % 2 = 1)
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, R.RowNo
from
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
inner join (SELECT ROW_NUMBER() OVER(ORDER BY M_Id) RowNo, M_Id FROM Members) R ON M.M_Id = R.M_Id
where
(S.M_Id <> M.M_Id) and
(R.RowNo % 2 = 1)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks!!
A couple of things:
Where is the snippet?
The joins and where clause aren't immediately obvious without some sample data.
Cheers
David