Two queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Two queries

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-08-26 : 06:20:43

Hi,

I have two queries dbo.qrycrtspsheet_PatEIDs and dbo.qrycrtspsheet. and I have another query which will require details from either of one these 2 queries at any one time (qryReminderletter - code shown below).

For example,
if you have 2 ProjectIDs - 12, 13. The contact details for 12 is dbo.qrycrtspsheet_PatEIDs and 13 is dbo.qrycrtspsheet. Presently, my query is ONLY reading only from dbo.qrycrtspsheet. Therefore details from dbo.qrycrtspsheet will show and not details from dbo.qrycrtspsheet_PatEIDs. How can I update the code to achieve a situation where it can read from any of the 2 queries at the same time.

Thanks

SELECT     TOP (100) PERCENT dbo.tblProjects.ISACProtocol, dbo.tblProjectScheduleEv.EvTypeID, dbo.tblProjectScheduleEv.EvDate, 
                      dbo.tblProjectScheduleEv.EvBFDate, dbo.tblProjects.CompletionDt, dbo.tblProjectScheduleEv.RemLetterGenDate, dbo.tblProjects.ProjectDesc, 
                      dbo.qrycrtspsheet.prac_no, dbo.qrycrtspsheet.main_doctor, dbo.qrycrtspsheet.address1, dbo.qrycrtspsheet.address2, dbo.qrycrtspsheet.address3, 
                      dbo.qrycrtspsheet.town, dbo.qrycrtspsheet.county, dbo.qrycrtspsheet.post_code
FROM         dbo.tblProjectScheduleEv INNER JOIN
                      dbo.tblProjectPractices ON dbo.tblProjectScheduleEv.ProjectPracticeID = dbo.tblProjectPractices.ProjectPracticeID INNER JOIN
                      dbo.tblProjects ON dbo.tblProjectPractices.ProjectID = dbo.tblProjects.ProjectID INNER JOIN
                      dbo.qrycrtspsheet ON dbo.tblProjects.ProjectID = dbo.qrycrtspsheet.ProjectID AND 
                      dbo.tblProjectPractices.Prac_no = dbo.qrycrtspsheet.prac_no INNER JOIN
                      dbo.tblProjectPatients ON dbo.tblProjects.ProjectID = dbo.tblProjectPatients.ProjectID AND 
                      dbo.qrycrtspsheet.prac_no = dbo.tblProjectPatients.Prac_no INNER JOIN
                      dbo.tblCurrProj ON dbo.qrycrtspsheet.ProjectID = dbo.tblCurrProj.Curr_ProjectID INNER JOIN
                      dbo.qryMaxEvTypeID ON dbo.tblProjects.ProjectID = dbo.qryMaxEvTypeID.ProjectID AND 
                      dbo.tblProjectScheduleEv.EvTypeID = dbo.qryMaxEvTypeID.MaxEvTypeID
WHERE     (dbo.tblProjects.ProjectStatus = 'True') AND (dbo.tblProjectPatients.No = 'true')
ORDER BY dbo.tblProjectPractices.ProjectID, dbo.tblProjectPractices.Prac_no

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-26 : 11:01:16

replace dbo.qrycrtspsheet in your query with below
(
SELECT required columns... FROM dbo.qrycrtspsheet
UNION ALL
SELECT required columns... FROM dbo.qrycrtspsheet_PatEIDs
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources