We help IT Professionals succeed at work.

Join two query to make it one

Hi,

I have two query

query 1)

select
d5.NO,
d.TYPE_CD_ENU,
d5.BUS_NM,
SUM(f.QTY),
D5.BUSINESS_ID
 from F_QTY f
LEFT OUTER JOIN D_JUNK d
on f.FK_JUNK_ID=d.SK_JUNK_ID
LEFT OUTER JOIN dbo.D_BUSINESS d5
on f.FK_ACCNT_ID=d5.SK_BUSINESS_ID
WHERE d5.NO ='102161'
group by
d5.EICB_NO,
d.TYPE_CD_ENU,
d5.BUS_NM,
d5.BUSINESS_ID

query 2)

select
e.Shift,
e.Return,
e.BUS_NM,
e.BUSINESS_ID
 from F_ACCT f
LEFT OUTER JOIN D_ACC_JUNK e
on f.FK_JUNK_ID=d.SK_JUNK_ID
LEFT OUTER JOIN dbo.D_BUSINESS d5
on f.FK_ACCNT_ID=d5.SK_BUSINESS_ID
WHERE d5.NO ='102161'

Now join query 1) and query 2)

to get below we can use BUSINESS_ID to join them

d5.NO,
d.TYPE_CD_ENU,
d5.BUS_NM,
SUM(f.QTY),
D5.BUSINESS_ID,
e.Shift,
e.Return,
e.BUS_NM,
e.BUSINESS_ID
Comment
Watch Question

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
SELECT
 NO,
 TYPE_CD_ENU,
 BUS_NM,
 QTY,
 query1.BUSINESS_ID,
 Shift,
 [Return],
 BUS_NM,
 query2.BUSINESS_ID
FROM (
    select
     d5.NO,
     d.TYPE_CD_ENU,
     d5.BUS_NM,
     SUM(f.QTY) AS QTY,
     D5.BUSINESS_ID
      from F_QTY f
     LEFT OUTER JOIN D_JUNK d
     on f.FK_JUNK_ID=d.SK_JUNK_ID
     LEFT OUTER JOIN dbo.D_BUSINESS d5
     on f.FK_ACCNT_ID=d5.SK_BUSINESS_ID
     WHERE d5.NO ='102161'
     group by
     d5.EICB_NO,
     d.TYPE_CD_ENU,
     d5.BUS_NM,
     d5.BUSINESS_ID
) AS query1
FULL OUTER JOIN (
     select
     e.Shift,
     e.[Return],
     e.BUS_NM,
     e.BUSINESS_ID
      from F_ACCT f
     LEFT OUTER JOIN D_ACC_JUNK e
     on f.FK_JUNK_ID=d.SK_JUNK_ID
     LEFT OUTER JOIN dbo.D_BUSINESS d5
     on f.FK_ACCNT_ID=d5.SK_BUSINESS_ID
     WHERE d5.NO ='102161'
) AS query2 ON
    query2.BUSINESS_ID = query1.BUSINESS_ID
--ORDER BY ...
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
both Q1 & Q2

LEFT OUTER JOIN dbo.D_BUSINESS D5 ON F.FK_ACCNT_ID = D5.SK_BUSINESS_ID
WHERE D5.NO = '102161'
There is no benefit to you in using that LEFT OUTER JOIN because every row MUST have D5.NO = '102161'

So just use INNER JOIN instead
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
sam2929, do you still need help with this question?