• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Select TOP row in One-Many relationship - MS SQL

I have following tables

REQUEST
------------
request_seq    request_name
1                      First Request
2                      Second Request

CONTACT
------------
contact_seq     contact_name
1                       John
2                       Mike
3                       Steve

REQUEST_CONTACT
-------------------------
request_contact_seq     request_seq    contact_seq    requested_date
1                                          1                      1                1/1/2010
2                                          1                      2                1/1/2010
3                                          1                      3                1/2/2010
1                                          2                      2                1/1/2010
2                                          2                      3                2/1/2010
3                                          2                      1                2/2/2010

The output should be
request_seq          request_name      first_contact_name
1                             First Request        John
2                             Second Request   Mike

The requirement is to select the contact person who requested first. If request_contact table is joined, we would get multiple rows for every contact_seq. Experts comments plz
0
Jeyakumar_mcp
Asked:
Jeyakumar_mcp
5 Solutions
 
sameer2010Commented:
Try this. SQL2K does not support row_number otherwise, this would have been much easier.
select a.request_seq as request_seq, a.request_name request_name, b.contact_name as first_contact_name
from REQUEST a, CONTACT b , REQUEST_CONTACT c
where
a.request_seq = c.request_seq
and b.contact_seq = c.contact_seq
and c.requested_date = 
(select min(d.requested_date) from REQUEST_CONTACT d where
d.request_seq = c.request_seq
)
and c.request_contact_seq = 
(
select min(e.request_contact_seq) from REQUEST_CONTACT e where
e.request_seq = c.request_seq
and e.requested_date = c.requested_date
)

Open in new window

0
 
ralmadaCommented:
I think it should be like this:
select a.request_seq, a.request_name, c.contact_name
from Request a
inner join request_contact b on a.request_seq = b.request_seq
inner join contact c on b.contact_seq = c.contact_seq
where c.requested_contact_seq = (select min(requested_contact_seq) from request_contact 
				where request_seq = c.request_seq)

Open in new window

0
 
HainKurtSr. System AnalystCommented:
here it is (if you did not get already)
select * from (
select rc.request_seq, r.request_name, c.first_contact_name, requested_date, 
       row_number() over (partititon by rc.request_seq, rc.contact_seq order by requested_date desc) as rn
  from REQUEST r, CONTACT c, REQUEST_CONTACT rc
 where rc.contact_seq = c.contact_seq and rc.request_seq = r.request_seq
) x where rn=1

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sameer2010Commented:
Yes, if request_contact_seq indicates the sequence in which the requests were placed, then simply the following would work
select a.request_seq as request_seq, a.request_name request_name, b.contact_name as first_contact_name
from REQUEST a, CONTACT b , REQUEST_CONTACT c
where
a.request_seq = c.request_seq
and b.contact_seq = c.contact_seq
and c.request_contact_seq = 
(
select min(e.request_contact_seq) from REQUEST_CONTACT e where
e.request_seq = c.request_seq
)

Open in new window

0
 
sarabhaiCommented:
select second table top row recordset as derived table and then use join with first table.
0
 
awking00Commented:
See attached.
query.txt
0
 
Jeyakumar_mcpAuthor Commented:
Firstly, I use SQL 2000 which does not allow ROW_NUMBER.

Secondly.. I don't know why, but I got all request_contact records when using the above queries. So I decided to have the logic that returns the contact name in a function and call it in the main query and it worked!!!

Thanks for all your time and support. You all guys deserve points :)
0
 
Jeyakumar_mcpAuthor Commented:
good job
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now