CODEWITHSUNDEEP
mysqlsqlmysql-5.7
Tanzeel
Tanzeel

Reputation: 4998

How to combine two select query with different columns

I know that union can be done to combine two queries if they have same number and type of columns. But I've a condition where I've to combine two select statement with different tables and different columns though 1 table is common in both i.e. PatientAppointment. Here are the two statements:

select p.CDRId, p.Gender,p.MRN,p.DoB as DOB,pa.AppointmentDateTime,cn.Description,ehv.ProgramName,
 cn.CreatedBy as CareTeamStaffMember,cn.Profile as Role,cn.Title as Credentials,
 date_format(pa.AppointmentDateTime, '%Y-%m') BillingMonth,
 ((case when duration like '% hour%' then substring_index(duration, ' hour', 1) * 60 else 0 end) +
(case when duration like '%min%' then substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)) as minutes
from Patient p inner join EnrollmentHistoryView ehv on ehv.CDRId = p.CDRId
inner join ClinicalNote cn on cn.CDRId = p.CDRId
inner join PatientAppointment pa on pa.CDRId = p.CDRId 
where p.CDRId='9493b505-03b9-46a0-b009-99b34f7a5d41' 
and ehv.ProgramName!='N/A'
group by p.CDRId, p.Gender, p.MRN, p.Dob, pa.AppointmentDateTime,cn.Description,cn.CreatedBy,cn.Profile,cn.Title,pa.Duration,ehv.ProgramName

UNION

SELECT AppointmentDateTime,
       duration,
       minutes,
       CASE WHEN @prev_month != BillingMonth
            THEN total >= 20
            WHEN @prev_total < 20 
            THEN 1
            ELSE 0 
            END 99457Elig,
       CASE WHEN @prev_month != BillingMonth
            THEN total >= 40
            WHEN @prev_total < 40
            THEN 1
            ELSE 0 
            END 99458Elig,
       @prev_month := BillingMonth BillingMonth,
       @prev_total := total total
FROM (select AppointmentDateTime,
             duration,
             @cur_dur := ((case when duration like '% hour%' then substring_index(duration, ' hour', 1) * 60 else 0 end) +
                         (case when duration like '%min%' then substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)) as minutes,

             CASE WHEN @year_month = date_format(AppointmentDateTime, '%Y-%m')
                  THEN @cum_sum := @cum_sum + @cur_dur
                  ELSE @cum_sum := @cur_dur
                  END total,
             @year_month := date_format(AppointmentDateTime, '%Y-%m') BillingMonth

      from PatientAppointment, (SELECT @year_month:='', @cum_sum:=0, @cur_dur:=0) variables
      ORDER BY AppointmentDateTime) subquery,
(SELECT @prev_month:=0, @prev_total:=0) variable
ORDER BY AppointmentDateTime

The query is too complex and I can't create the data set also. Please help me with the approach. Give me some suggestion at least. I'll try it myself.

Upvotes: 0

Views: 1751

Answers (1)

Dark Knight
Dark Knight

Reputation: 6531

Consider your first query as Query1 and second query as Query2, you can use simple join between these two.

As you said PatientAppointment table is common in both, use its primary key(CDRId) as joining between these two. So your query would look like.

SELECT * 
FROM ( Query1 ) AS table1
INNER JOIN ( Query2) AS table2 ON table1.CDRId = table2.CDRId;

Upvotes: 2

Related Questions