?
Solved

Select TOP row in One-Many relationship - MS SQL

Posted on 2010-01-12
8
Medium Priority
?
269 Views
Last Modified: 2012-05-08
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
Comment
Question by:Jeyakumar_mcp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 13

Expert Comment

by:sameer2010
ID: 26294375
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
 
LVL 41

Accepted Solution

by:
ralmada earned 120 total points
ID: 26294502
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
 
LVL 58

Assisted Solution

by:HainKurt
HainKurt earned 120 total points
ID: 26294714
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
DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

 
LVL 13

Assisted Solution

by:sameer2010
sameer2010 earned 120 total points
ID: 26294751
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
 
LVL 9

Assisted Solution

by:sarabhai
sarabhai earned 120 total points
ID: 26295362
select second table top row recordset as derived table and then use join with first table.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 120 total points
ID: 26297820
See attached.
query.txt
0
 
LVL 1

Author Comment

by:Jeyakumar_mcp
ID: 26308037
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
 
LVL 1

Author Closing Comment

by:Jeyakumar_mcp
ID: 31676139
good job
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

765 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