How do I write a query to do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

How do I write a query to do this?

Author

Topic

PatLee
Starting Member

3 Posts

Posted - 2002-08-23 : 16:51:15

I'm having difficulty trying to figure out the best way to go about creating a query for this in SQL Server 2k... any insight would be much appreciated...

My tables are pretty much set up as below (simplified a bit)

tblCompetitorList - listing of individual athletes and their personal info (contains about 2,000+ rows)
- CompetitorID
- FirstName
- LastName

tblImageList - a table containing all images (contains about 50,000+ rows)
- ImageID
- ImageFileName
- ImageDate
- ActiveDate

tblCompetitorImageLink - a linking table which associates each COMPETITOR with IMAGES of them (contains 50,000+ rows)
- ID
- PersonID
- ImageID

I want to create a stored procedure that will return a recordset which displays the 'FirstName' and 'LastName' of each person and for each row single 'ImageID' and 'ImageFileName' values, where the 'ImageFileName' is that of the newest image for that respective person. To reiterate, I want a recordset that will be a listing of each individual PERSON accompanied with a single IMAGE of them for each row.

Here's is the field output I want for each row --

==

CompetitorID | FirstName | LastName | ImageID | ImageFileName

... and Sorted by LastName, FirstName

==

I've tried doing this myself, but I'm always getting a recordset which lists individual competitors multiple times.

What's the best way to go about this?

Thanks a lot

Patrick.

r937
Posting Yak Master

112 Posts

Posted - 2002-08-23 : 18:50:56

select CompetitorID, FirstName, LastName
     , ImageID, ImageFileName
  from tblCompetitorList 
inner
  join tblCompetitorImageLink 
    on CompetitorID 
     = tblCompetitorImageLink.PersonID
inner
  join tblImageList 
    on tblCompetitorImageLink.ImageID 
     = tblImageList.ImageID
   and ImageDate = 
       ( select max(ImageDate)
           from tblImageList 
          where PersonID = tblCompetitorImageLink.PersonID )

i think

rudy
http://rudy.ca/

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-23 : 18:58:01

Edited by - ajarnmark on 08/23/2002 18:58:31

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources