min/max issue when joining tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

min/max issue when joining tables

Author

Topic

lbeese
Starting Member

24 Posts

Posted - 2010-02-04 : 16:35:18

I have 2 tables: Member_Interview and Member_Interview_Response

They are structured as such:
Interview
member_id Interview_seq interview_id interview_date
1234 1 500048 1/21/2010
1234 2 500048 1/26/2010
2356 1 500050 1/10/2010

Interview_Response
member_id interview_seq question_id response
1234 1 502157 A
1234 2 502157 B
1234 1 502062 A
1234 2 502062 A

I am trying to pull all of the answers to the first interview completed. I have written the below query, but am pulling the response from both questions 502157 for member 1234 because the response is different. I am not sure how to go about excluding that. I can't do a min on the response, as the first response will not always be lowest. Any assistance is appreciated.

select i.member_id,
min(i.interview_seq),
min(i.interview_date),
r.question_id,
r.response
from ccmsdba.member_interview i
join ccmsdba.member_interview_resp r
on i.member_id = r.member_id
and i.interview_seq = r.interview_seq
where i.interview_id = 500048
group by i.member_id, r.question_id, r.response

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-04 : 16:47:29

What is your expected output?

lbeese
Starting Member

24 Posts

Posted - 2010-02-04 : 16:55:58

member_id interview_seq Interview_date question_id response
1234 1 1/21/2010 502157 A
1234 1 1/21/2010 502062 A

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-04 : 17:03:40

Can you not use

where r.interview_seq = 1

instead of

where i.interview_id = 500048

declare @Interview table
(member_id int,Interview_seq int,interview_id int,interview_date datetime)
insert @Interview
select 1234, 1, 500048, '1/21/2010'
union all select 1234, 2, 500048, '1/26/2010'
union all select 2356, 1, 500050, '1/10/2010'

declare @Interview_Response table
(member_id int,interview_seq int,question_id int,response varchar(1))
insert @Interview_Response
select 1234, 1, 502157, 'A'
union all select 1234, 2, 502157, 'B'
union all select 1234, 1 ,502062, 'A'
union all select 1234, 2 ,502062, 'A'

select a.member_id,a.Interview_seq,a.interview_date,b.question_id,b.response
from @Interview a
inner join @Interview_Response b on a.member_id = b.member_id and a.Interview_seq = b.interview_seq
where b.interview_seq = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources