How to properly JOIN different queries where the different queries may return a different amount of results

Question

I am using SQL Server 2008. I have three different queries that I would like to union together, but am unsure of how to properly go about it, as the structure of the queries are different. My first query, creates two tables to insert records into for a comparison, where the MAX of a column for a visit is returned. Ex:

Query:

SET ANSI_NULLS OFF
GO

-- VARIABLE DECLARATION
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME

-- VARIABLE INITIALIZATION
SET @STARTDATE = '6/1/12';
SET @ENDDATE = '1/1/13';

--#############################################################################################
-- TABLE DECLARATION WHICH WILL BE USED TO GET THE MAX ORDERED DATE                           #
--                                                                                            #
DECLARE @T1 TABLE (ENCOUNTER VARCHAR(200), PT_NAME VARCHAR(500), MRN VARCHAR(200),          --#
                   LOS VARCHAR(200), PT_LOC VARCHAR(500), PT_DISPO VARCHAR(500),            --# 
                   LAB_NAME VARCHAR(500), LAB_VALUE VARCHAR(40),LOWER_LIMIT VARCHAR(30),    --# 
                   UPPER_LIMIT VARCHAR(30), HISTORY VARCHAR(10), HAS_HISTORY VARCHAR(10),   --#
                   AB_CODE VARCHAR(30), ORDER_ENTERED VARCHAR(500))                         --#
DECLARE @T2 TABLE (ENCOUNTER2 VARCHAR(200), PT_NAME2 VARCHAR(500), MRN2 VARCHAR(200),       --# 
                   LOS2 VARCHAR(200), PT_LOC2 VARCHAR(500), PT_DISPO2 VARCHAR(500),         --#
                   LAB_NAME2 VARCHAR(500), LAB_VALUE2 VARCHAR(40),LOWER_LIMIT2 VARCHAR(30), --#
                   UPPER_LIMIT2 VARCHAR(30), HISTORY2 VARCHAR(10), HAS_HISTORY2 VARCHAR(10),--# 
                   AB_CODE2 VARCHAR(30), ORDER_ENTERED2 VARCHAR(500))                       --#
--                                                                                            #
--#############################################################################################

--##       TABLE INSERTIONS     ##
--###########################################################################################
--## WHAT GETS PUT INTO TABLE 1 ##
INSERT INTO @T1
SELECT
A.VisitIDCode,
A.ClientDisplayName,
A.IDCode,
A.LOS,
A.CurrentLocation,
A.DischargeDisposition,
A.ItemName,
A.Value,
A.ReferenceLowerLimit,
A.ReferenceUpperLimit,
A.IsHistory,
A.HasHistory,
A.AbnormalityCode,
A.Entered

FROM
(
-- COLUMN SELECTION
SELECT CV.VisitIDCode, CV.ClientDisplayName, CV.IDCode,DATEDIFF(DD,CV.ADMITDTM,CV.DISCHARGEDTM)AS 'LOS',
CV.CurrentLocation, CV.DischargeDisposition, BO.ItemName, BO.Value,
BO.ReferenceLowerLimit, BO.ReferenceUpperLimit, BO.IsHistory, BO.HasHistory, BO.AbnormalityCode,
BO.Entered

-- DB USED: SCM
FROM CV3ClientVisit CV
JOIN CV3BasicObservation BO
ON CV.GUID = BO.ClientVisitGUID

WHERE CV.AdmitDtm BETWEEN @STARTDATE AND @ENDDATE
AND CV.TypeCode = 'INPATIENT'
AND BO.Value IS NOT NULL
AND (BO.ItemName LIKE '%SODIUM LEVEL%'
OR BO.ITEMNAME LIKE '%HEMOG%')
)A 

--##       TABLE INSERTIONS     ##
--###########################################################################################
--## WHAT GETS PUT INTO TABLE 2 ##
INSERT INTO @T2
SELECT
B.VisitIDCode,
B.ClientDisplayName,
B.IDCode,
B.LOS,
B.CurrentLocation,
B.DischargeDisposition,
B.ItemName,
B.Value,
B.ReferenceLowerLimit,
B.ReferenceUpperLimit,
B.IsHistory,
B.HasHistory,
B.AbnormalityCode,
B.Entered

FROM
(
-- COLUMN SELECTION
SELECT CV.VisitIDCode, CV.ClientDisplayName, CV.IDCode,DATEDIFF(DD,CV.ADMITDTM,CV.DISCHARGEDTM) AS 'LOS', 
CV.CurrentLocation,CV.DischargeDisposition, BO.ItemName, BO.Value,BO.ReferenceLowerLimit, BO.ReferenceUpperLimit,   
BO.IsHistory, BO.HasHistory, BO.AbnormalityCode,BO.Entered

-- DB USED: SCM
FROM CV3ClientVisit CV
JOIN CV3BasicObservation BO
ON CV.GUID = BO.ClientVisitGUID


WHERE CV.AdmitDtm BETWEEN @STARTDATE AND @ENDDATE
AND CV.TypeCode = 'INPATIENT'
AND BO.Value IS NOT NULL
AND (BO.ItemName LIKE '%SODIUM LEVEL%'
OR BO.ITEMNAME LIKE '%HEMOG%')
)B

--###########################################################################################
--## HERE IS WHERE WE DO TABLE COMPARISONS ##

SELECT
DISTINCT T1.ENCOUNTER,
T1.PT_NAME AS 'PT NAME', T1.MRN AS 'MRN', T1.LOS AS 'LOS', T1.PT_LOC AS 'PT LOC',
T1.PT_DISPO AS 'PT DISPO', T2.LAB_NAME2 AS 'LAB NAME', T2.LAB_VALUE2, T2.LOWER_LIMIT2 AS 'LOWER LIMIT',
T2.UPPER_LIMIT2 AS 'UPPER LIMIT', T2.AB_CODE2 AS 'AB CODE', T2.ORDER_ENTERED2

FROM @T1 T1
JOIN @T2 T2
ON T1.MRN = T2.MRN2

WHERE
T1.ENCOUNTER = T2.ENCOUNTER2
AND T1.ORDER_ENTERED < T2.ORDER_ENTERED2
AND T2.ORDER_ENTERED2 = (
                        SELECT MAX(TEMP.ORDER_ENTERED2)
                        FROM @T2 TEMP
                        WHERE T1.MRN = TEMP.MRN2
                        )

Results

CLIENT ID    VISIT ID     ARRIVE DATE     VALUE     DATE VALUE ORDERED
.......................................................................
1      |     1      |     1/1/13     |    5     |   1/1/13
1      |     1      |     1/1/13     |    6     |   1/2/13 <- returned row

Where this query will return the row with the date value ordered of 1/2/13.

The second query counts for me how many times the Client in the Client ID column came inside of a 12 month time frame. So for the above it would return something like Query 2:

-- VARIABLE DECLARATION
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME

-- INITIALIZE VARIABLES
SET @STARTDATE = '6/1/12';
SET @ENDDATE = '1/1/13';

-- COLUMN SELECTION
SELECT DISTINCT CV.IDCode AS 'MRN', COUNT(CV.IDCODE) AS 'COUNT OF IP VISITS'

FROM CV3ClientVisit CV

WHERE CV.AdmitDtm BETWEEN @STARTDATE AND @ENDDATE
AND CV.TypeCode LIKE '%INPATIENT'
AND CV.VisitStatus IN(
'ADM',
'DSC'
)
GROUP BY CV.IDCode
ORDER BY COUNT(CV.IDCode) DESC

Results: Client ID Count of Visits ............................... 1 | 2

The third and final query tells me how many times they did something, for example

Query 3:

Declare @procedures Table (MRN varchar(20), Patient varchar(80), VisitID varchar(20), Admit datetime, Disch datetime, SurgProc varchar(200), ProcDesc varchar(200))
insert into @procedures


select cv.IDCode,cv.ClientDisplayName,cv.VisitIDCode,AdmitDtm,cv.DischargeDtm,ed.Description,ed.text from CV3ClientVisit cv
left join cV3ClientEventDeclaration ed
    on cv.GUID=ed.ClientVisitGUID
        where ed.typecode = 'Surgery'
        and cv.AdmitDtm > '6/30/12' and cv.AdmitDtm <='1/1/13'
        and Status = 'Active'

    select visitid, COUNT(visitid)as '#Surg Procs' from @procedures
        group by visitid

Results:

CLIENT ID     VISIT ID     ARRIVE DATE     DEPART DATE     COUNT OF PR VISITS
...............................................................................
1         |   1         |  1/1/13       |  1/3/13       |  3

So my question is, how do I properly JOIN all of these queries together in order to get just one result? There will be times where a client may not have a result for one of the queries.

Thank You,

PS I hope all of this is clear

Answer 1

I suspect that ou don't want a union but instead want join. This will bring all the fields on one row:

SELECT q1.CLIENT_ID, q1.VISIT_ID, q1.ARRIVE_DATE,
       q3.DEPART_DATE, q3.LENGTH_OF_STAY,
       q1.VALUE, q1.DATE_VALUE_ORDERED,
       q4.Count_of_Visits
FROM (<query1>
     ) q1 left outer join
     (<query2>
     ) q2
     on q1.client_id = q2.client_id left outer join
     (<query3>
     ) q3
     on q1.client_id = q3.client_id and q1.client_id = q3.visit_id left outer join
     (<query4>
     ) q4
     on q1.client_id = q4.client_id and q1.client_id = q4.visit_id

I am also guessing that you can get all the data in a single, simpler query. That is, the four subqueries are probably not necessary. If you are interested in investigating that, you should ask another question.

Answer 2

One approach would be to identify all columns selected in all the queries you want to union, then add appropriately named and positioned columns with null values (where the relevant columns are missing from the queries) to each query - ie. something like:

SELECT CLIENT_ID,
       VISIT_ID,
       ARRIVE_DATE,
       NULL DEPART_DATE,
       NULL LENGTH_OF_STAY,
       VALUE,
       DATE_VALUE_ORDERED,
       NULL Count_of_Visits,
       1 QUERY_ID
FROM   {Query 1}
UNION ALL
SELECT CLIENT_ID,
       NULL VISIT_ID,
       NULL ARRIVE_DATE,
       NULL DEPART_DATE,
       NULL LENGTH_OF_STAY,
       NULL VALUE,
       NULL DATE_VALUE_ORDERED,
       Count_of_Visits,
       2 QUERY_ID
FROM   {Query 2}
UNION ALL
SELECT CLIENT_ID,
       VISIT_ID,
       ARRIVE_DATE,
       DEPART_DATE,
       LENGTH_OF_STAY,
       NULL VALUE,
       NULL DATE_VALUE_ORDERED,
       NULL Count_of_Visits,
       3 QUERY_ID
FROM   {Query 3}
UNION ALL
SELECT CLIENT_ID,
       VISIT_ID,
       ARRIVE_DATE,
       DEPART_DATE,
       NULL LENGTH_OF_STAY,
       NULL VALUE,
       NULL DATE_VALUE_ORDERED,
       Count_of_PR_Visits Count_of_Visits,
       4 QUERY_ID
FROM   {Query 4}