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.S LsendToId=acade micoffice.AOid
SELECT SLlastName FROM academicoffice
WHERE schoolrequest.S LbillToId=acade micoffice.AOid
SELECT SLlastName FROM academicoffice
WHERE schoolrequest.S LrequestorId=ac ademicoffice.AO id
SELECT * FROM diaryentry WHERE diaryentry.DEke yValue=10
AND diaryentry.DEde letedDate 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(SLsend ToId, 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 4 1697
<ct***@litsol.c om> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.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.S LsendToId=acade micoffice.AOid SELECT SLlastName FROM academicoffice WHERE schoolrequest.S LbillToId=acade micoffice.AOid SELECT SLlastName FROM academicoffice WHERE schoolrequest.S LrequestorId=ac ademicoffice.AO id
SELECT * FROM diaryentry WHERE diaryentry.DEke yValue=10 AND diaryentry.DEde letedDate 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(SLsend ToId, 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
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_Gene ral_CP1_CI_AS NULL
,
[SLcopyTypeID] [varchar] (7) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[SLreceivedDate] [datetime] NULL ,
[SLrequestorID] [int] NULL ,
[SLbillToID] [int] NULL ,
[SLsendToID] [int] NULL ,
[SLclaimNumber] [varchar] (64) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[SLbillToFile] [varchar] (39) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[SLsubjectName] [varchar] (63) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[SLsetsRequested] [int] NULL ,
[SLrequestedDate] [smalldatetime] NULL ,
[SLestimatedUnit s] [int] NULL ,
[SLactualUnits] [int] NULL ,
[SLcolorUnits] [decimal](2, 0) NOT NULL ,
[SLinstructions] [varchar] (198) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[SLnotes] [varchar] (183) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[SLcurrentStatus] [decimal](3, 0) NULL ,
[SLstatusUpdated] [datetime] NULL ,
[SLenteredDate] [smalldatetime] NULL ,
[SLenteredBy] [varchar] (11) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[SLmodifiedDate] [datetime] NOT NULL ,
[SLmodifiedBy] [varchar] (11) COLLATE SQL_Latin1_Gene ral_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
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=a 1.AOid
LEFT JOIN academicoffice a2 ON sr.SLBillToID=a 2.AOid
LEFT JOIN academicoffice a3 ON sr.SLrequestorI D=a3.AOid
LEFT JOIN (SELECT *
FROM diaryentry
WHERE DEdeletedDate IS NULL) d ON sr.SLid=d.DEkey Value
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
(ct***@litsol.c om) 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.S LsendToId=acade micoffice.AOid SELECT SLlastName FROM academicoffice WHERE schoolrequest.S LbillToId=acade micoffice.AOid SELECT SLlastName FROM academicoffice WHERE schoolrequest.S LrequestorId=ac ademicoffice.AO id
SELECT * FROM diaryentry WHERE diaryentry.DEke yValue=10 AND diaryentry.DEde letedDate 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(SLsend ToId, 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.SLrequestorTo Id = 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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: DG |
last post by:
Hi,
Can anyone advise how to execute multiple statements in a single query
batch. For example-
update customers set customer_name = 'Smith' where customer_name =
'Smyth';
select * from customers;
I can execute each statement individually but get the 'you have an error in
|
by: Jeremy |
last post by:
I have built a form that calls queries. I have the first 2 set up as
select queries, and the third set up as a make table query. When
multiple users are on this application at the same time, they get
errors or the data is overwritten while they are sitting there. If I
do all select queries, 1. the records pop up if I use a "RUN" command
button, or if I have my "Report" command button set to the last query
then each time they hit one of...
|
by: MHenry |
last post by:
Hi,
I know virtually nothing about creating Macros in Access.
I would appreciate some help in creating a Macro or Macros that
automatically run(s) 14 Queries (three Make Table Queries, and 11
Append Queries) when the Database is first opened, and then again
anytime I invoke the Macro (somehow) after the database is already
open. The latter function is useful, because the queries must be rerun
every time data is added, deleted, or...
|
by: dskillingstad |
last post by:
I've been struggling with this problem for some time and have tried
multiple solutions with no luck.
Let me start with, I'm a novice at Access and I'm not looking for
someones help to design my database,just help in getting me pointed in
the right direction.
I have a database with 8 tables, which from what I have read, cannot be
linked on a single form, and be updatable. I have created a query
which includes all 8 tables, and then...
|
by: Dave Edwards |
last post by:
I understand that I can fill a datagrid with multiple queries, but I cannot
figure out how to fill a dataset with the same query but run against
multiple SQL servers, the query , table structure and username, password etc
will be exactly the same for each server, the only thing that will change is
the server name. Idealy I would like to get the server names from a seperate
dataset so there could be any number of servers, allthough in...
| |
by: mattcatmattcat |
last post by:
I have a VB7 aspx file I am creating that requires multiple queries each
dependant on the previous queries results. If I run these queries in foxpro,
I just run a query then create a cursor with the results of that cursor, then
run another query based on that cursor etc. My question is, how do I do this
in a VB7 aspx file? Below I have the code for the first query but I am not
sure where to go from here to run the rest of the queries that...
|
by: beretta819 |
last post by:
Ok, so I apologize in advance for the wordiness of what follows... (I am not looking for someone to make this for me, but to point me in the right direction for the steps I need to take.)
I was nominated at work to design a "system" that would allow us to track the performance of our employees. So I thought Access. Should be simple enough because I have made simple DB's with it before. I was wrong. This has turned into a bigger deal than I...
|
by: vaiism |
last post by:
I am creating a report that outputs the contact information and details about a water treatment plant, and needs to include information about people who work there.
If I tie all the information to a single query the report contains a full set of information for the plant for each of the people who work there. If I embed multiple queries into the report, the user has to input the search criteria once for each of the queries that is being...
|
by: shriil |
last post by:
Hi
I have this database that calculates and stores the incentive amount
earned by employees of a particular department. Each record is
entered
by entering the Date, Shift (morn, eve, or night) and the 'employee
name'. There is another table which assigns an ID to the Shifts, i.e.
1,2 and 3 for morn, eve & night shifts respectively. From the mother
table, the incentive is calculated datewise for each employee as per
his shift duty. In...
|
by: Supermansteel |
last post by:
My team at work uses Cognos to run multiple queries to pull in data. Then they take that data and import into Access and then usually run an Append Query to run a RND function to pull out a few samples for them to review the accounts. I was making a suggestion to my team that maybe if we only ran one Cognos query and pulled in all of the columns that everyone needs to pull samples for each of there tests that this would be better on the time...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |