-
joining/selecting more than 2 tables
Newbie here, but then you will see that shortly.
I am having difficulty writing a stored procedure joining 3 or more tables. The technologies I am using are asp.net and sql.
We have a main data table with our ID keys, (ex: siteID, locationID) then numerous look up tables(tbleSite, tblLocation)
with the descriptions corresponding to the ID subjects with and ID key. (Ex: tblSite has siteID and siteDesc)
The first join works, but after that I get an Out of Range error on my aspx page for the column stated on the second join in the locations table.(WorkOriginText)
So, it's like it cannot find it's way back to the main table to proceed to the second join.
This process has to repeat itself a number of times, as we grab the ID from the main table then go to another table to grab the ID's description. They all refer back to the main table.
I'm not sure what to do.
CREATE PROCEDURE proc_ReportAppEvent
(
@StartDate datetime,
@EndDate datetime
)
AS
SET NOCOUNT ON
SELECT
tblDD1532.ApplicationEventDate,
tblSiteOfApplication.SiteOfApplication from tblSiteOfApplication Inner Join tblMainData On tblSiteofApplication.SiteOfApplication_ID = tblMainData.SiteOfApplication_ID
--up to here works.
--here's where I start the second join
Select
tblWorkOrigin.WorkOriginText from tblWorkOrigin Inner Join tblMainData On tblWorkOrigin.WorkOrigin_ID = tblMainData.WorkOrigin_ID
Where (tblDD1532.ApplicationEventDate Between @StartDate AND @EndDate)
ORDER BY tblDD1532.ApplicationEventDate
Set NOCOUNT OFF
GO
THANKS!
-
I dont know the sql server you are using but the select and join looks somewhat strange to me.
You want just 3 columns?
You might try it this way - but I do not see yet where and how the tblDD1532 is joined in....
-----------------------8<--------------------
CREATE PROCEDURE proc_ReportAppEvent
(
@StartDate datetime,
@EndDate datetime
)
AS
SET NOCOUNT ON
SELECT
tblDD1532.ApplicationEventDate,
tblSiteOfApplication.SiteOfApplication,
tblWorkOrigin.WorkOriginText
from tblSiteOfApplication
Inner Join tblMainData On
tblSiteofApplication.SiteOfApplication_ID = tblMainData.SiteOfApplication_ID
Inner Join tblWorkOrigin On
tblWorkOrigin.WorkOrigin_ID = tblMainData.WorkOrigin_ID
Where (tblDD1532.ApplicationEventDate Between @StartDate AND @EndDate)
ORDER BY tblDD1532.ApplicationEventDate
Set NOCOUNT OFF
GO
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|