SQL query to join two different queries with different number of columns

Question

I have two queries here :

Query 1:

SELECT allowdeductname_en, SUM(SFD_comp_value_tax@;emp_id) as                                                                                                                                      
GGG                                                                                                                                                                                                 
FROM TPYDPROCMTDD md                                                                                       
LEFT JOIN TPYDPROCMTDH mh on md.procmtdh_id = mh.procmtdh_id and 
md.company_id = mh.company_id                                                                                                                                                                                                                                                    
WHERE md.allowdeducttype = 'A'                                                                                                                                                                                                                                                     
AND md.company_id = '13565'
AND mh.company_id = '13565'
AND year(mh.paydate) = 2017                                                           
AND month(mh.paydate) = 1 
AND mh.costcenter_code = 99990001
group by allowdeductname_en
order by allowdeductname_en

Query 2:

SELECT distinct allowdeductname_en                                                                                                                                                                                              
FROM TPYDPROCMTDD md                                                                                       
LEFT JOIN TPYDPROCMTDH mh on md.procmtdh_id = mh.procmtdh_id and 
md.company_id = mh.company_id                                                                                                                                                                                                                                                    
WHERE md.allowdeducttype = 'A'                                                                                                                                                                                                                                                     
AND md.company_id = '13565'
AND mh.company_id = '13565'
AND year(mh.paydate) = 2017                                                           
AND month(mh.paydate) = 1
order by allowdeductname_en

Result For Query 1 and Query 2:

Is there anyway that I can intersect, join or union both of these queries so that it will look something like this :

Expected Results:

I have tried so many different ways of doing it but still couldn't get the results I want. Guys PLEASE help !

Edit your question and put the sample data and desired results as text in the question. — Gordon Linoff
– Gordon Linoff, Commented Mar 28, 2018 at 8:02
Make it easy to assist you. Keep column names and data short and easy to read even for us who doesn't speak your language. — jarlh
– jarlh, Commented Mar 28, 2018 at 8:05
You could simply outer join query 1 to query 2. However, your queries are not properly written. For example your outer joins don't work (and maybe they are not even supposed to). I suppose that you could write a much simpler query. What table does allowdeductname_en reside in? And SUM(SFD_comp_value_tax@;emp_id) is probably a typo. Can you please correct it? — Thorsten Kettner
– Thorsten Kettner, Commented Mar 28, 2018 at 8:12

Michał Turczyn · Accepted Answer · 2018-03-28 08:10:48Z

2

Well, this is most straighforward solution:

select * from (/*query number 2*/) [a]
left join (/*query number 1*/) [b] 
on [a].allowdeductname_en = [b].allowdeductname_en

But you can accomplish it in one query, since both queries use the same table, but in order to provide that solution, I'd need to see your data.

answered Mar 28, 2018 at 8:10

Michał Turczyn

41.3k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Louied · Accepted Answer · 2018-03-28 08:22:38Z

select t2.allowdeductname_en, GGG=isnull(t1.GGG,0)
 from 
(SELECT distinct allowdeductname_en                                                                                                                                                                                              
FROM TPYDPROCMTDD md                                                                                       
LEFT JOIN TPYDPROCMTDH mh on md.procmtdh_id = mh.procmtdh_id and 
md.company_id = mh.company_id                                                                                                                                                                                                                                                    
WHERE md.allowdeducttype = 'A'                                                                                                                                                                                                                                                     
AND md.company_id = '13565'
AND mh.company_id = '13565'
AND year(mh.paydate) = 2017                                                           
AND month(mh.paydate) = 1 ) t2

LEFT JOIN 

(SELECT allowdeductname_en, SUM(SFD_comp_value_tax@;emp_id) as                                                                                                                                      
GGG                                                                                                                                                                                                 
FROM TPYDPROCMTDD md                                                                                       
LEFT JOIN TPYDPROCMTDH mh on md.procmtdh_id = mh.procmtdh_id and 
md.company_id = mh.company_id                                                                                                                                                                                                                                                    
WHERE md.allowdeducttype = 'A'                                                                                                                                                                                                                                                     
AND md.company_id = '13565'
AND mh.company_id = '13565'
AND year(mh.paydate) = 2017                                                           
AND month(mh.paydate) = 1 
AND mh.costcenter_code = 99990001
group by allowdeductname_en)  t1 on t2.allowdeductname_en=t1.allowdeductname_en


this is not the best query but it give you the result you want.

EzLo · Accepted Answer · 2018-03-28 08:18:21Z

You can use a LEFT JOIN with ISNULL between the two:

;WITH FirstQuery AS
(
    SELECT 
        allowdeductname_en, 
        SUM(SFD_comp_value_tax@;emp_id) as    GGG                                                                                                                                                                                                 
    FROM TPYDPROCMTDD md                                                                                       
    LEFT JOIN TPYDPROCMTDH mh on md.procmtdh_id = mh.procmtdh_id and 
    md.company_id = mh.company_id                                                                                                                                                                                                                                                    
    WHERE md.allowdeducttype = 'A'                                                                                                                                                                                                                                                     
    AND md.company_id = '13565'
    AND mh.company_id = '13565'
    AND year(mh.paydate) = 2017                                                           
    AND month(mh.paydate) = 1 
    AND mh.costcenter_code = 99990001
    group by allowdeductname_en
),
SecondQuery AS
(
    SELECT distinct allowdeductname_en                                                                                                                                                                                              
    FROM TPYDPROCMTDD md                                                                                       
    LEFT JOIN TPYDPROCMTDH mh on md.procmtdh_id = mh.procmtdh_id and 
    md.company_id = mh.company_id                                                                                                                                                                                                                                                    
    WHERE md.allowdeducttype = 'A'                                                                                                                                                                                                                                                     
    AND md.company_id = '13565'
    AND mh.company_id = '13565'
    AND year(mh.paydate) = 2017                                                           
    AND month(mh.paydate) = 1
)
SELECT
    S.allowdeductname_en,
    ISNULL(N.GGG, 0) AS GGG
FROM
    SecondQuery AS S
    LEFT JOIN FirstQuery AS N ON S.allowdeductname_en = N.allowdeductname_en

Thorsten Kettner · Accepted Answer · 2018-03-28 08:52:27Z

0

The queries merely differ in the condition costcenter_code = 99990001. Move this to your aggregation function SUM:

select allowdeductname_en,
  sum(case when mh.costcenter_code = 99990001 then sfd_comp_value_tax end) as ggg
from tpydprocmtdd md
left join tpydprocmtdh mh on md.procmtdh_id = mh.procmtdh_id
                          and md.company_id = mh.company_id
                          and year(mh.paydate) = 2017
                          and month(mh.paydate) = 1
where md.allowdeducttype = 'A'
and md.company_id = 13565
group by allowdeductname_en
order by allowdeductname_en;

edited Mar 28, 2018 at 8:52

answered Mar 28, 2018 at 8:17

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

SQL query to join two different queries with different number of columns

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related