MS Sql Identical Table Consolidation

I have two identical tables that I would like to consolidate.

Tbl_library1
Author       book      publisher qty

author_a booka  pub1      12
author_a bookb  pub1      34
author_a bookb  pub2      34
author_b bookb  pub2      34
author_c bookc  pub2      89
author_d bookd  pub3       35
author_e booke  pub2      65

Tbl_library2
Author       book      publisher qty

author_a booka  pub1      86
author_a bookb  pub1      75
author_a bookb  pub2      78
author_c bookc  pub2      65
author_z booke  pub2      23

required result

Author       book      publisher qty
author_a booka  pub1      98
author_a bookb  pub1      109
author_a bookb  pub2      112
author_b bookb  pub2      34
author_c bookc  pub2      154
author_z booke  pub2      23
author_d bookd  pub3       35
author_e booke  pub2      65

summarising identical record quantities, aswell as listing unique quantities.

The only way i can think of doing this is doing a inner join on the two tables

select tbl1.book, tbl1.author. tbl1.publisher, tbl1.qty + tbl2.qty as total_qty
from tb1 inner jonin on tbl2
 where tbl1.book = tbl2.book and tbl1. book  = tbl2. book and tbl1.publisher = table2.publisher.

This would give all the common values. i was then going to get a sub select to retrieve all records not in the result from tbl1 then tbl2 then union the three sets of results together.

I'm just looking at getting this to work, but as I would envisage this to be a very common task I was concerned I was missing an obvious method or function to achieve the above that isn't so long winded. Perhaps by making a unique filed and working with that.
(author + book + publisher as unique_filed)

Is there a more efficient way to achieve my requirement ?

Many thanks in anticipation.


king_sgukAsked:
Who is Participating?
 
cminearConnect With a Mentor Commented:
I spotted some issues with the code sample I copied from you.
 - 'tbl1' instead of 'tbl'; and
 - add condition check on 'author' in the two tables, rather than book twice;
 - remove the space in front of 'book'.
select tbl1.book, tbl1.author. tbl1.publisher, 
  COALESCE(tbl1.qty, 0) + COALESCE(tbl2.qty, 0) as total_qty
from tb11 FULL OUTER JOIN tbl2
 on (tbl1.author = tbl2.author and tbl1book  = tbl2. book 
     and tbl1.publisher = tbl2.publisher)

Open in new window

0
 
cminearCommented:
Instead of an INNER JOIN, use a FULL OUTER JOIN.  And then use COALESCE to make sure you are adding quantities in the case of nulls.
select tbl1.book, tbl1.author. tbl1.publisher, 
  COALESCE(tbl1.qty, 0) + COALESCE(tbl2.qty, 0) as total_qty
from tb1 FULL OUTER JOIN tbl2
 on (tbl1.book = tbl2.book and tbl1. book  = tbl2. book and tbl1.publisher = tbl2.publisher)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.