February 28, 2008 at 8:22 am
Hi,
I have two queries that I want to join together and put into a view.
So the first query will work as the main query followed by query 2 that works as a sub query.
Hope this makes sense- see below
The MAIN QUERY
--------------------
SELECT case ST.CurrentSickTerm
when 'LT' then 'Long Term'
when 'ST' then 'Short Term'
when '' then 'NULL'
END as SickTerm,
case SE.DayNightWorker
when '1' then 'N'
when '0' then 'D'
when '' then 'NULL'
END as DayNight,
ST.CurrentSickTerm, SMS.Surname, SMS.Forenames, SMS.Title, SMS.[Job Title], SMS.SMSWard, SE.FirstDate, SE.LastDate,
SE.BaseWard, SE.PersonnelNumber, SE.BaseDirectorate, SE.SickCategory, SE.SickType, SE.SickReason, SE.IR1Number, SE.RIDDORDate,
SE.SickEpisodeID, SE.RTWInterview, SE.Deleted, HS.HospitalSiteDesc
FROM dbo.tblSicknessEpisode SE INNER JOIN
dbo.tblSMSStaff SMS ON SE.PersonnelNumber = SMS.[Personal Number] INNER JOIN
dbo.viewCurrentStaffSickTerms ST ON SE.PersonnelNumber = ST.[Personal Number] INNER JOIN
dbo.tblHospitalSite HS ON SE.BaseDirectorate = HS.HospitalSite
WHERE (SE.LastDate IS NULL) OR
(SE.LastDate >= '1/12/2006') AND (ST.CurrentSickTerm <> '') AND (SE.Deleted = 0)
ORDER BY SMS.Surname, SMS.Forenames, SE.FirstDate
----------------------------------------
the SUB QUERY
select v.PersonnelNumber, v.SickEpisodeID,
AD.ActionDescription, AD.ActionDescID, A.ActionAgreedDate,A.ActionValidFrom,
from ViewMgrsRpt v LEFT JOIN dbo.tblAction A ON v.SickEpisodeID = A.SickEpisodeID LEFT JOIN dbo.tblActionDescription AD ON A.ActionDescription =
AD.ActionDescID
where A.Deleted = 0
order by v.PersonnelNumber, A.ActionAgreedDate
---------
February 28, 2008 at 8:31 am
Hello Ritesh,
It would be better if you can post your tables structure so that it will help us to help you in a better way. It is good that you have posted the queries but it might not be sufficient for anybody here to help and educate you in the best way.
Thanks
Lucky
February 28, 2008 at 8:41 am
Couple things.
why does this need to be a subquery
Does the subquery join (inner) on the columns returned?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 28, 2008 at 8:44 am
The second query should pull back info from the first and itself. so the second query will match on the first and pull this like First Date, Last Date ,etc
At the moment they dont join at all.
February 28, 2008 at 8:58 am
the rough table struct for Query1
tblSicknessEpsisode tblSmsStaff StaffSick hospitalSite/b]SickID PersonnelNo PersonnelNo hospitalSite
PersonnelNo Title CurrentSick HospitalDesc
day/night Fname
First Date Surname
last Date
BaseDirectorate
ETC
the table struct for Query2
Query1 tblAction ActionDescription SickID Action Id ActiondescID
PersonnelNo SickID ActionDescription
day/night ActionDesc
First Date
last Date
BaseDirectorate
ETC
February 28, 2008 at 10:55 am
ritesh (2/28/2008)
The second query should pull back info from the first and itself. so the second query will match on the first and pull this like First Date, Last Date ,etcAt the moment they dont join at all.
Then you probably want the first query as a derived table that the second query can pull from, or something along those lines. You can also look at simply combining the queries, joining all the associated tables together.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply