Joining two select statements into one

Question

How do I join these statements to return results together?

Query 1:

select scheduleentry.scheduledate,
    patientdemographics.accountnumber,
    patientdemographics.firstname,
    patientdemographics.middleinitial,
    patientdemographics.lastname,
    patientdemographics.address1,
    patientdemographics.address2,
    patientdemographics.city,
    patientdemographics.state,
    patientdemographics.zipcode,
    patientdemographics.dateofbirth,
    patientdemographics.sex,
    patientdemographics.hometelephone,
    patientinsuranceprofiles.insurance1name,
    patientinsuranceprofiles.insurance1policynumber,
    patientinsuranceprofiles.insurance1policygroupnumber,
    patientinsuranceprofiles.insurance1guarantorfirstname as insuredfirstname,
    patientinsuranceprofiles.insurance1guarantormiddleinitial as insuredmiddleinitial,
    patientinsuranceprofiles.insurance1guarantorlastname as insuredlastname,
    patientinsuranceprofiles.insurance1guarantoraddress1 as insuredaddress1,
    patientinsuranceprofiles.insurance1guarantoraddress2 as insuredaddress2,
    patientinsuranceprofiles.insurance1guarantorcity as insuredcity,
    patientinsuranceprofiles.insurance1guarantorstate as insuredstate,
    patientinsuranceprofiles.insurance1guarantorzipcode as insuredzip,
    patientinsuranceprofiles.insurance1guarantordateofbirth as insureddob,
    patientinsuranceprofiles.insurance1guarantorsex as insuredsex,
    patientinsuranceprofiles.insurance1guarantortelephonenumber as insuredhomenumber,
    insurances.eligibilitypayornumber
from patientdemographics
inner join patientinsuranceprofiles
on  patientdemographics.accountnumber = patientinsuranceprofiles.patientaccountnumber
inner join scheduleentry
on  patientinsuranceprofiles.patientaccountnumber = scheduleentry.patientaccount
inner join insurances
on  patientinsuranceprofiles.insurance1mnemonic = insurances.mnemonic
where datediff(d, getdate(), scheduledate) = 1
    and patientinsuranceprofiles.activeflag = 1
order by scheduledate asc

Query 2:

select scheduleentry.scheduledate,
    patientdemographics.accountnumber,
    patientdemographics.firstname,
    patientdemographics.middleinitial,
    patientdemographics.lastname,
    patientdemographics.address1,
    patientdemographics.address2,
    patientdemographics.city,
    patientdemographics.state,
    patientdemographics.zipcode,
    patientdemographics.dateofbirth,
    patientdemographics.sex,
    patientdemographics.hometelephone,
    patientinsuranceprofiles.insurance2name,
    patientinsuranceprofiles.insurance2policynumber,
    patientinsuranceprofiles.insurance2policygroupnumber,
    patientinsuranceprofiles.insurance2guarantorfirstname as insuredfirstname,
    patientinsuranceprofiles.insurance2guarantormiddleinitial as insuredmiddleinitial,
    patientinsuranceprofiles.insurance2guarantorlastname as insuredlastname,
    patientinsuranceprofiles.insurance2guarantoraddress1 as insuredaddress1,
    patientinsuranceprofiles.insurance2guarantoraddress2 as insuredaddress2,
    patientinsuranceprofiles.insurance2guarantorcity as insuredcity,
    patientinsuranceprofiles.insurance2guarantorstate as insuredstate,
    patientinsuranceprofiles.insurance2guarantorzipcode as insuredzip,
    patientinsuranceprofiles.insurance2guarantordateofbirth as insureddob,
    patientinsuranceprofiles.insurance2guarantorsex as insuredsex,
    patientinsuranceprofiles.insurance2guarantortelephonenumber as insuredhomenumber,
    insurances.eligibilitypayornumber
from patientdemographics
inner join patientinsuranceprofiles
on  patientdemographics.accountnumber = patientinsuranceprofiles.patientaccountnumber
inner join scheduleentry
on  patientinsuranceprofiles.patientaccountnumber = scheduleentry.patientaccount
inner join insurances
on  patientinsuranceprofiles.insurance2mnemonic = insurances.mnemonic
where datediff(d, getdate(), scheduledate) = 1
    and patientinsuranceprofiles.activeflag = 1
order by scheduledate asc

score 0 · Answer 1 · answered Oct 10 '17 at 11:24

You could use UNION ALL in between the two queries e.g.

SELECT ...
FROM ...
WHERE...

UNION ALL

SELECT ...
FROM ....
WHERE...

You can read about the differences in UNION and UNION ALL here: What is the difference between UNION and UNION ALL?

You could also use CTE - if there is a column you can join on, e.g.

;WITH Query1 AS
(
 SELECT Column1
 FROM ...
)
,Query2 AS
( 
 SELECT Column1
 FROM ...
)
SELECT     *
FROM       Query1 AS Q1
INNER JOIN Query2 AS Q2 ON Q1.Column1 = Q2.Column1

score 0 · Accepted Answer · answered Oct 10 '17 at 11:31

One method is to use UNION ALL approach as others suggested.

(Query 1)
union all
(Query 2)

For your case, perhaps case and little modification to the join condition will work. Please try the below:

select se.scheduledate,
    pd.accountnumber,
    pd.firstname,
    pd.middleinitial,
    pd.lastname,
    pd.address1,
    pd.address2,
    pd.city,
    pd.state,
    pd.zipcode,
    pd.dateofbirth,
    pd.sex,
    pd.hometelephone,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1name                     else pip.insurance2name end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1policynumber             else pip.insurance2policynumber end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1policygroupnumber        else pip.insurance2policygroupnumber end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantorfirstname       else pip.insurance2guarantorfirstname as insuredfirstname end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantormiddleinitial   else pip.insurance2guarantormiddleinitial as insuredmiddleinitial end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantorlastname        else pip.insurance2guarantorlastname as insuredlastname end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantoraddress1        else pip.insurance2guarantoraddress1 as insuredaddress1 end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantoraddress2        else pip.insurance2guarantoraddress2 as insuredaddress2 end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantorcity            else pip.insurance2guarantorcity as insuredcity end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantorstate           else pip.insurance2guarantorstate as insuredstate end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantorzipcode         else pip.insurance2guarantorzipcode as insuredzip end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantordateofbirth     else pip.insurance2guarantordateofbirth as insureddob end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantorsex             else pip.insurance2guarantorsex as insuredsex end,
    case when i.mnemonic = pip.insurance1mnemonic then pip.insurance1guarantortelephonenumber else pip.insurance2guarantortelephonenumber as insuredhomenumber end,
    i.eligibilitypayornumber
from patientdemographics pd
inner join patientinsuranceprofiles pip
on  pd.accountnumber = pip.patientaccountnumber
inner join scheduleentry se
on  pip.patientaccountnumber = se.patientaccount
inner join insurances i
on  i.mnemonic in (pip.insurance1mnemonic, pip.insurance2mnemonic)
where datediff(d, getdate(), scheduledate) = 1
    and pip.activeflag = 1
order by scheduledate asc

@JohnMolina take the order by of the query you are running first. — Keith, Oct 10 '17 at 11:35

Joining two select statements into one

2 Answers2