Multiple Queries

[ctoth]

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).

Any ideas?

Thanks,
M

[Simon Hayes]

<ct***@litsol.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

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).

Any ideas?

Thanks,
M

What do you mean by "combine"? If you want to return all the results in one
result set, then you can use UNION ALL, assuming that each query's result
set has the same columns and data type (although that doesn't seem to be the
case in your example). If that isn't helpful, then you'll have to give some
more information - DDL for the tables, some sample data and the expected
results.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

[ctoth]

Simon, well I mean I need to pull all the data from the schoolrequest
table. But the schoolrequest table has some columns with ID numbers
that correspond to rows in the academicoffice and diaryentry tables.

CREATE TABLE [schoolrequest] (
[SLid] [int] IDENTITY (1, 1) NOT NULL ,
[SLjobTypeID] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SLcopyTypeID] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SLreceivedDate] [datetime] NULL ,
[SLrequestorID] [int] NULL ,
[SLbillToID] [int] NULL ,
[SLsendToID] [int] NULL ,
[SLclaimNumber] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SLbillToFile] [varchar] (39) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SLsubjectName] [varchar] (63) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SLsetsRequested] [int] NULL ,
[SLrequestedDate] [smalldatetime] NULL ,
[SLestimatedUnits] [int] NULL ,
[SLactualUnits] [int] NULL ,
[SLcolorUnits] [decimal](2, 0) NOT NULL ,
[SLinstructions] [varchar] (198) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SLnotes] [varchar] (183) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SLcurrentStatus] [decimal](3, 0) NULL ,
[SLstatusUpdated] [datetime] NULL ,
[SLenteredDate] [smalldatetime] NULL ,
[SLenteredBy] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SLmodifiedDate] [datetime] NOT NULL ,
[SLmodifiedBy] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

Sample of one row:

5334
TRJTINT
TRRTALL
2005-01-17 15:32:26.000
870
NULL
1059
5837
D
433498
10-18-02
NULL
Deborah
Schirmer
1
NULL
NULL
185
0
Subpeona
NULL
100
2005-01-17 15:35:07.000
2005-01-17 00:00:00
djohns
2005-01-17 15:35:07.000
djohns

[Stu]

I think I get what you're driving at; you need to use aliases to rename
both the columns and the tables to retrieve a meaningful dataset.

SELECT sr.ColumnList,
a1.Name as SendToName,
a2.Name as BillToName,
a3.Name as RequestorName,
d.ColumnList
FROM schoolrequest sr
LEFT JOIN academicoffice a1 ON sr.SLsendToID=a1.AOid
LEFT JOIN academicoffice a2 ON sr.SLBillToID=a2.AOid
LEFT JOIN academicoffice a3 ON sr.SLrequestorID=a3.AOid
LEFT JOIN (SELECT *
FROM diaryentry
WHERE DEdeletedDate IS NULL) d ON sr.SLid=d.DEkeyValue
WHERE sr.SLid=10

If that ain't it, try to post some sample data as well as an example of
the desired result.

HTH,
Stu

[Erland Sommarskog]

(ct***@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, es****@sommarskog.se

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