JOIN but only get TOP 1 record

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)

JOIN but only get TOP 1 record

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2003-02-06 : 13:01:54

Hello-

I'm trying to write a query that takes three tables (tblSegments, tblSegmentsNotes, and tblNotes). I'd like to get all of the segments, but only the first note for each segment. I can write the query that will give me all of the segments with all of the notes, but I'm drawing a bit of a blank on how to get just the first note. I had considered using derived tables, but I can't get the corresponding note to come up. Here is what I have for getting all the segments with all of the notes.

SELECT     tblSegments.segmentTravelingOn, tblSegments.segmentDirections, tblSegments.segmentDistance, tblNotes.note
FROM         tblSegments LEFT OUTER JOIN
                      tblNotes INNER JOIN
                      tblSegmentsNotes sn ON tblNotes.noteID = sn.noteID ON tblSegments.segmentID = sn.segmentID
WHERE     (tblSegments.segmentDate = '5/10/2003')

Thanks-

Nick

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-02-06 : 13:05:00

What defines a note as being "first"?

Jay White
{0}

Nick
Posting Yak Master

155 Posts

Posted - 2003-02-06 : 13:35:14

Honestly it doesn't matter, but in practice it would have the lower noteID. 'ORDER BY noteID ASC'

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-02-06 : 13:44:22

add a NOT EXISTS construct with a correlated subquery to your query ...


....

and not exists (
    select 1
    from
        tblsegmentnotes
    where
        sn.segmentid = segmentid and
        sn.noteid > noteid )

Jay White
{0}

Nick
Posting Yak Master

155 Posts

Posted - 2003-02-06 : 14:04:58

Very slick. Thank you much.

Nick
Posting Yak Master

155 Posts

Posted - 2003-02-08 : 11:31:55

As an Addendum to this question. If I wanted to get the Top 2 records instead, and put them in two seperate columns how would I do that?

I'm trying to export some data to an excel spreadsheet and need two columns Note1 and Note2 to display the first two notes in the database for each row.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-08 : 20:20:44

quote:

As an Addendum to this question. If I wanted to get the Top 2 records instead, and put them in two seperate columns how would I do that?

I'm trying to export some data to an excel spreadsheet and need two columns Note1 and Note2 to display the first two notes in the database for each row.

Join to the table twice instead of once.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources