Join Two Tables

I have this code and works well, however this code works with records in the same table. Now i need split the records in two tables. One for records doc='O' (Table Orcs) and another for records doc='F' (Table Obrs). How can join these tables to get same result

SELECT code, descrip,
SUM(CASE WHEN doc='O' THEN Quant ELSE 0 END) as [Quant-Orc],
SUM(CASE WHEN doc='F' THEN Quant ELSE 0 END) as [Quant-Sales],
SUM(CASE WHEN doc='O' THEN Quant ELSE 0 END) - SUM(CASE WHEN doc='F' THEN Quant ELSE 0 END) as Balance
FROM ObrLin
GROUP BY code, descrip

Open in new window

rflorencioAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Assuming they have the same exact columns:

SELECT code, descrip,
SUM(CASE WHEN doc='O' THEN Quant ELSE 0 END) as [Quant-Orc],
SUM(CASE WHEN doc='F' THEN Quant ELSE 0 END) as [Quant-Sales],
SUM(CASE WHEN doc='O' THEN Quant ELSE 0 END) - SUM(CASE WHEN doc='F' THEN Quant ELSE 0 END) as Balance
FROM (SELECT * FROM Orcs UNION ALL
      SELECT * FROM Obrs) AS myUnion
GROUP BY code, descrip

Open in new window

You could also consolidate your SUMs within subqueries, as follows:

SELECT  code,
        descrip,
        [Quant-Orc],
        [Quant-Sales],
        [Quant-Orc] - [Quant-Sales] AS Balance
FROM   (SELECT code, descrip, SUM(Quant) AS [Quant-Orc]
        FROM   Orcs
        GROUP BY code, descrip) Orcs
LEFT JOIN ( SELECT code, descrip, SUM(Quant) AS [Quant-Sales]
        FROM   Obrs
        GROUP BY code, descrip) Obrs
ON      Obrs.code = Orcs.code

Open in new window

0
PortletPaulfreelancerCommented:
here is a way:
SELECT
        code
      , descrip
      , SUM(Quant_Orc)                    AS [Quant-Orc]
      , SUM(Quant_sales)                  AS [Quant-Sales]
      , SUM(Quant_Orc) - SUM(Quant_sales) AS Balance
FROM (
        SELECT
                code
              , descrip
              , SUM(quant)        AS Quant_Orc
              , CAST(NULL AS int) AS Quant_sales -- or whatever the data type is
        FROM Table_Orcs
        GROUP BY
                code
              , descrip

        UNION ALL

                SELECT
                        code
                      , descrip
                      , CAST(NULL AS int) AS Quant_Orc -- or whatever the data type is
                      , SUM(quant)        AS Quant_sales
                FROM Table_Obrs
                GROUP BY
                        code
                      , descrip

     ) AS ObrLin
GROUP BY
         code
       , descrip
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
like this ?

SELECT code, descrip,
coalesce(SUM(CASE WHEN doc='O' THEN Quant END),0) as [Quant-Orc],
coalesce(SUM(CASE WHEN doc='F' THEN Quant END),0) as [Quant-Sales],
SUM(quant * CASE doc When 'O' THEN 1 ELSE -1 END) as Balance
FROM (SELECT code,descrip,quant,doc FROM Orcs where doc='O'
      UNION ALL
      SELECT code,descrip,quant,doc FROM Obrs where doc='F') AS x
GROUP BY code, descrip
order by 1
0
rflorencioAuthor Commented:
I appreciate all the contributions, but I need to make some modifications and the query of  PortletPaul it seemed the easiest for me.
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.