Combining SELECT statements
-
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
-
This is not pretty but it works :
Select SLlastName from dbo.academicoffice A inner join dbo.schoolrequest S on A.AOid in (S.SLsendToId, S.SLbillToId, S.SLrequestorId)
Use isnull(S.SLsendToId), ... if the fields can be set to null
-
Wow, I think that works for at least some of the queries!
But that still leaves me with the last query
(SELECT * FROM diaryentry WHERE diaryentry.DEkeyValue=10
AND diaryentry.DEdeletedDate IS NULL)
...how would that be combined?
Thanks,
M!
-
Depends of what you expect as a result...
Can you send the table definition, some sample data and the expected results of the query.
-
I'm not sure exactly what you wanted, but here's the sql script to create the table:
CREATE TABLE [dbo].[diaryentry] (
[DEid] [decimal](5, 0) NULL ,
[DEentryTypeID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEkeyValue] [decimal](4, 0) NOT NULL ,
[DEaltKeyValue] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEdescription] [varchar] (515) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEenteredBy] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEenteredDate] [datetime] NULL ,
[DEtriggerDate] [smalldatetime] NULL ,
[DEackDate] [smalldatetime] NULL ,
[DEemailTrigger] [tinyint] NOT NULL ,
[DEmodifiedBy] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEmodifiedDate] [datetime] NOT NULL ,
[DEdeletedDate] [datetime] NULL ,
[DEtargetUserID] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
And here's a sample row:
97
ENRRCV
34
NULL
File logged in
nkrasman
4/1/2003 11:33:51 AM
NULL
NULL
0
NULL
4/1/2003 11:33:51 AM
NULL
NULL
-
Yes but how do you need this data to be combined with the other rows of the other tables?
What do you expect as a result of the query?
-
Remi,
Oh ok,
This query(combination of queries) is for an ASP webpage. The page will display information from the schoolrequest, diaryentry, and academicoffice tables. The schoolrequest table has 3 columns that contain ID numbers that correspond to the primary ID of the academicoffice.
The page needs info from the diaryentry table because the diaryentry table has info entered in by students. The diaryentry table has a column named DEkeyValue that corresponds to the primary ID column of the schoolrequest table(SLid).
So, in short, I expect to see the query return all the data in the schoolrequest table that corresponds to a specific ID that the user of the website selects. So, for example, if they select SLid=5, then the query returns all the data in the schoolrequest table where SLid=5. And hence, I need to pull the relavent data from the academicoffice(WHERE SLsendToId=AOid, SLbillToId=AOid, SLrequestorId=AOid, but that will be 3 different AOid #'s, not the same one) and diaryentry(WHERE DEkeyValue=SLid) tables as well.
I hope that helps. I know what I want, just not how to combine all the queries I wrote above.
Thanks,
M
-
I don't see an easy way out right now... seems to me that you'll need more than one query to pull this out (or a big bunch of left joins).
Seems to me like this data is related but not the of same nature. But maybe I got the webpage picture wrong in my head...
-
Given your original queries:
SELECT SQ.*
, ST.SLlastName AS SendTo
, BT.SLlastName AS BillTo
, R.SLlastName AS Requestor
, DE.*
FROM schoolrequest SQ
INNER JOIN academicoffice ST ON SQ.SLsendToId=ST.AOid
INNER JOIN academicoffice BT ON SQ.SLbillToId=BT.AOid
INNER JOIN academicoffice R ON SQ.SLrequestorId=R.AOid
INNER JOIN diaryentry DE ON SQ.SLid=DE.DEkeyValue
AND DE.DEdeletedDate IS NULL
WHERE SQ.SLid=10
Would allow a single query to return a single RecordSet.
The only issues I can see are: if you have duplicate column names in the schoolrequest and diaryentry tables; and if any of the JOIN columns could result in no match and don't want to restrict by that JOIN, then change INNER to LEFT.
Andy
-
I also noticed the diaryentry table structure, if you plan to use the Decimal columns in ADO, you will be best served by CASTing your Decimal(5,0) AS int and Decimal(4,0) AS smallint
I also question the use for smalldatetime due to the datatype limit of June 6, 2079. While I will not live to see this, sounds like the Y2K problem all over again!
Andy
-
Takes 2 minutes to fix (yeah right
). -
Sometimes on queries like this, I use a view to gather most of the fields I want in one place and then query that view. I can and often do use a join to add additional fields to the query.
-
Be carefull with that technic... You could be losing performance if you are nesting too many queries.
-
Thanks Remi. Help clarify for me. How is a view, which joins tables with a query, so much different than a sub-query?
How about a technique where I Joins simpler views together in a query. I have found a view to be somewhat quicker in some instances than a complex join and it is certianly easier to figure out what I did six months ago when something breaks.
Normally we are only working with 10 million records or less for this kind of query and the tables are indexed for the specific queries.
-
Basically sql server has to materialize the view into a pseudo table (like a derived table) to access the data. If you're using too many nested views ,assuming you're not using all the cols all the time, you're wasting precious I/O.
Also it comes a point that the query optmizer can't figure out the best plan to access the data.
However I agree with you that it can be easier to understand the code a few months later. But there's also a little thing called comments that you can be using to that effect
.
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply