How to join two tables in one view?

I am trying to create a view where I can see the items that have been planned to be shipped and have not been shipped, and the items have have been shipped but were not planned to. In order to do this I have 2 tables with different data in them.

Table SC (actually shipped):

+---------+-----------------+----------------------+-------------+
| item_id | source_location | destination_location | shipped_qty |
+---------+-----------------+----------------------+-------------+
| 001     |    California   |    South_Carolina    |      80     |
+---------+-----------------+----------------------+-------------+
| 001     |    California   |    South_Carolina    |      0      |
+---------+-----------------+----------------------+-------------+
| 001     |    California   |         Texas        |      20     |
+---------+-----------------+----------------------+-------------+
| 003     |      Texas      |    South_Carolina    |     200     |
+---------+-----------------+----------------------+-------------+
| 004     |  South_Carolina |         Texas        |      30     |
+---------+-----------------+----------------------+-------------+
| 004     |  South_Carolina |         Texas        |      10     |
+---------+-----------------+----------------------+-------------+

Table SO (plan to ship items):

+---------+-----------------+----------------------+---------------+
| item_id | source_location | destination_location | planned_order |
+---------+-----------------+----------------------+---------------+
| 001     |    California   |    South_Carolina    |      100      |
+---------+-----------------+----------------------+---------------+
| 001     |    California   |    South_Carolina    |      100      |
+---------+-----------------+----------------------+---------------+
| 001     |    California   |         Texas        |       10      |
+---------+-----------------+----------------------+---------------+
| 003     |      Texas      |    South_Carolina    |      200      |
+---------+-----------------+----------------------+---------------+
| 004     |  South_Carolina |         Texas        |      300      |
+---------+-----------------+----------------------+---------------+
| 004     |  South_Carolina |         Texas        |      50       |
+---------+-----------------+----------------------+---------------+

So in this case, for example, since the item 001 has three different planned orders from California to South Carolina, I don’t want it to show all the three orders in the view, I want it to be only in one row, but sum all the planned orders together, as showed below.

Desired Outcome:

+---------+----------------+-----------------+-------------+-------------+
| item_id |   source_loc   | destination_loc | shipped_qty | planned_qty |
+---------+----------------+-----------------+-------------+-------------+
| 001     |   California   |  South_Carolina |      80     | 200         |
+---------+----------------+-----------------+-------------+-------------+
| 001     |   California   |      Texas      |      20     | 10          |
+---------+----------------+-----------------+-------------+-------------+
| 003     |      Texas     |  South_Carolina |     200     | 200         |
+---------+----------------+-----------------+-------------+-------------+
| 004     | South_Carolina |      Texas      |      40     | 350         |
+---------+----------------+-----------------+-------------+-------------+

I have tried this so far:

SELECT  o.source_location,
        o.destination_location,
        o.item_id,
        o.planned_order,
        c.shipped_qty
        FROM SO_TRANSFER o, SC_TRANSFER c

But this hasn’t worked since the shipped_qty does not match the item and this code also does not add the orders together.

By the way, I am using Microsoft SQL Server 2012. Thank you!

Answer

I think you want:

select coalesce(s.item_id, p.item_id) as item_id,
       coalesce(s.source_location, p.source_location) as source_location,
       coalesce(s.destination_location, p.destination_location) as destination_location,
       coalesce(s.shipped_qty, 0) as shipped_qty,
       coalesce(planned_qty, 0) as planned_qty
from (select item_id, source_location, destination_location, sum(shipped_qty) as shipped_qty
      from sc
      group by item_id, source_location, destination_location
     ) s full join
     (select item_id, source_location, destination_location, sum(planned_qty) as planned_qty
      from so
      group by item_id, source_location, destination_location
     ) p
     on s.item_id = p.item_id and
        s.source_location = p.source_location and
        s.destination_location = p.destination_location;

Advertisement

Answer