?
Solved

Ignore duplicate records in select statement

Posted on 2009-04-15
5
Medium Priority
?
180 Views
Last Modified: 2012-05-06
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?
0
Comment
Question by:allanmark
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 24151805
Hi,

A couple of things:
Where is the snippet?

The joins and where clause aren't immediately obvious without some sample data.

Cheers
  David
0
 

Author Comment

by:allanmark
ID: 24151913
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

Open in new window

0
 

Author Comment

by:allanmark
ID: 24152195
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)  

0
 
LVL 35

Accepted Solution

by:
David Todd earned 1000 total points
ID: 24152319
Hi,

If the above works for you then fine.

I have a different suggestion in the snippet.

If you had a flag that differentiated the couple into male/female, or primary/secondary, then it gets a little simpler. I'm being a little old fashioned here, and assuming male is primary.

HTH
  David
use tempdb
go
 
/*
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
 
*/
 
-- Drop Create Temp Table
if object_id( N'tempdb..#Couple', N'U' ) is not null 
	drop table #Couple;
	
create table #Couple 
	(
	C_ID int
	, C_Surname varchar( 20 )
	)
	
insert #Couple( 
	C_ID
	, C_Surname	
	)
	select 1, 'Bennett'
	union all select 2, 'Thomas'
	union all select 3, 'Jones'
	union all select 4, 'Brown'
	union all select 5, 'Van Der Merwe'
	union all select 6, 'Peters'
	union all select 7, 'Smith'
		
select *
from #Couple
 
-- Drop Create Temp Table
if object_id( N'tempdb..#Member', N'U' ) is not null 
	drop table #Member;
	
create table #Member 
	(
	M_ID int
	, M_Couples_Link int
	, M_Surname varchar( 20 )
	, M_First_Name varchar( 20 )
	, Sex char( 1 )
	)
	
insert #Member(
	M_ID
	, M_Couples_Link
	, M_Surname
	, M_First_Name
	, Sex
	)
	select 10, 1, 'Lewis', 'Bev', 'F'
	union all select 9, 1, 'Bennett', 'Allan', 'M'
	union all select 12, 2, 'Thomas', 'Wendy', 'F'
	union all select 11, 2, 'Thomas', 'Dave', 'M'
	union all select 14, 3, 'Jones', 'Kelly', 'F'
	union all select 13, 3, 'Jones', 'Paul', 'M'
	union all select 16, 4, 'Brown', 'Melanie', 'F'
	union all select 15, 4, 'Brown', 'Mark', 'M'
	union all select 18, 5, 'V/D Merwe', 'Lesley', 'F'
	union all select 17, 5, 'V/D Merwe', 'Sean', 'M'
	union all select 20, 6, 'Peters', 'Wendy', 'F'
	union all select 19, 6, 'Peters', 'Bill', 'M'
	union all select 22, 7, 'Smith', 'Shirley', 'F'
	union all select 21, 7, 'Smith', 'Robert', 'M'
	
select *
from #Member
 
-- your select
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
  #Couple C left join #member M on C.C_Id = M.M_Couples_Link
  left join #Member S on C.C_Id = S.M_Couples_Link
where
  (S.M_Id <> M.M_Id) 
;
 
-- The start of what you would like
select 
	c.C_ID
	, c.C_Surname
from #Couple c
;
 
-- Couple with the individual coupleID's
select 
	c.C_ID
	, c.C_Surname
	, (
		select top 1 mi.M_ID
		from #Member mi
		where 
			mi.M_Couples_Link = c.C_ID
		order by
			mi.M_ID asc
	) as FirstMember_ID
	, (
		select top 1 mi.M_ID
		from #Member mi
		where 
			mi.M_Couples_Link = c.C_ID
		order by
			mi.M_ID desc
	) as LastMember_ID			
from #Couple c
;
 
-- Couple with additional details
select
	co.C_ID
	, co.C_Surname
	, m1.M_ID
	, m1.M_Couples_Link
	, m1.M_Surname
	, m1.M_First_Name
	, m2.M_ID
	, m2.M_Couples_Link
	, m2.M_Surname
	, m2.M_First_Name
from (
	select 
		c.C_ID
		, c.C_Surname
		, (
			select top 1 mi.M_ID
			from #Member mi
			where 
				mi.M_Couples_Link = c.C_ID
			order by
				mi.M_ID asc
		) as FirstMember_ID
		, (
			select top 1 mi.M_ID
			from #Member mi
			where 
				mi.M_Couples_Link = c.C_ID
			order by
				mi.M_ID desc
		) as LastMember_ID			
	from #Couple c
	) co
left outer join #Member m1 
	on m1.M_ID = co.FirstMember_ID  
left outer join #Member m2
	on m2.M_ID = co.LastMember_ID
;
	
-- using sex flag
select 
	c.C_ID
	, c.C_Surname
	, M.M_ID
	, M.M_Couples_Link
	, M.M_Surname
	, M.M_First_Name
	, F.M_ID
	, F.M_Couples_Link
	, F.M_Surname
	, F.M_First_Name
from #Couple c
inner join #Member M
	on m.M_Couples_Link = c.C_ID
	and m.Sex = 'M'
inner join #Member F
	on m.M_Couples_Link = c.C_ID
	and m.Sex = 'F'
;

Open in new window

0
 

Author Closing Comment

by:allanmark
ID: 31570656
Many thanks!!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question