将两个select语句合并为一个

How do I join these statements to return results together?

如何加入这些语句以将结果一起返回？

Query 1:

查询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:

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

2 个解决方案

#1

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

一种方法是使用UNION ALL方法，如其他人建议的那样。

(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

#2

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

您可以在两个查询之间使用UNION ALL，例如

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?

你可以在这里阅读UNION和UNION ALL的不同之处：UNION和UNION ALL有什么区别？

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

您也可以使用CTE - 如果您有可以加入的列，例如

;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

#1