Combine 2 queries toegether and put into a view

Question

Combine 2 queries toegether and put into a view

ritesh-385882

Mr or Mrs. 500

Points: 533
More actions
February 28, 2008 at 8:22 am

#187130

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Couple things.

why does this need to be a subquery

Does the subquery join (inner) on the columns returned?

ritesh-385882 Mr or Mrs. 500 Points: 533 More actions · Answer 3

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.

ritesh-385882 Mr or Mrs. 500 Points: 533 More actions · Answer 4

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

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

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 ,etc
At 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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning