Posted by Erland Sommarskog on 06/16/05 01:00

(ctoth@litsol.com) writes:
> I'm writing an ASP page for a project and it requires multiple queries.
> However, I'm trying to combine multiple SELECT statements, but can't
> figure out a way that actually works.
>
> Basically, here are the SELECT statements I want to combine:
>
> SELECT * FROM schoolrequest WHERE SLid=10
>
> SELECT SLlastName FROM academicoffice
> WHERE schoolrequest.SLsendToId=academicoffice.AOid
> SELECT SLlastName FROM academicoffice
> WHERE schoolrequest.SLbillToId=academicoffice.AOid
> SELECT SLlastName FROM academicoffice
> WHERE schoolrequest.SLrequestorId=academicoffice.AOid
>
> SELECT * FROM diaryentry WHERE diaryentry.DEkeyValue=10
> AND diaryentry.DEdeletedDate IS NULL
>
> The academicoffice table just contains basic contact info, but needs to
> be used 3 times in the query to get specific contact info for 3
> different contacts(SLsendToId, SLbillToId, SLrequestorId)
>
> The diaryentry table contains info entered in by students. The
> DEkeyValue is actually the primary id of the schoolrequest table(SLid).

As a complete guess, this could be what you are looking for:

SELECT s.*, a1.SLlastname, a2.SLlastName, a3.SLlastName
FROM schoolrequest s
JOIN academicoffice a1 ON s.SLsendToId = a1.AOid
JOIN academicoffice a2 ON s.SLbillToId = a2.AOid
JOIN academicoffice a3 ON s.SLrequestorToId = a3.AOid
WHERE s.SLid = 10

Here, I have not included the diaryentry table. I leave that as a exercise
to find that out yourself.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]