Selecting Nth record in result set

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)

Selecting Nth record in result set

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-27 : 08:54:06

Michael writes "I am using Openldap to serve up our contact info stored in a MSSQL database. It has the ability to Query our databse when a request is made for a contacts information.

For a given contact there is one primary email, and up to four other email addresses. I can grab the primary email no problem, and return the single value.

I can also do a query that will return all of the other emails, but I need to be able to have 4 separate queries. One that can return the 2nd email in the list, one for the 3rd.....

Here is the SQL select that will return the 4 secondary emails, it's ugly but so is the table we are pulling the information from

SELECT contsupref
FROM contsupp,contact1
WHERE contsup.rectype='P' AND contsupp.contact='E-mail' AND
contact1.accountno = contsupp.accountno AND
contsupp.zip like '_0__'

SQL Server 2000 8.00.760
ODBC Version 03.52.0000
Windows NT4"

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-04-30 : 00:08:30

Michael,

Assuming you want all the emails in one record, you could do the following.

SELECT C.email1, E1.email as Email1, E2.email as Email2, E3.email as Email3, E4.email as Email4
FROM Contact C LEFT JOIN EmailTbl E1 on C.ContactID = E1.ContactID
LEFT JOIN EmailTbl E2 on C.ContactID = E2.ContactID
LEFT JOIN EmailTbl E3 on C.ContactID = E3.ContactID
LEFT JOIN EmailTbl E4 on C.ContactID = E4.ContactID

It's a bit messy, but it will work.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources