How to combine values inside a common column from two different tables?-CodePudding

Sorry if the title is confusing, since English isn't my first language. But I'll try to describe my tables thoroughly.

I have 3 tables inside my database named: Restaurant1, Restaurant2, and ItemList

Tables Restaurant 1 and Restaurant 2 both contain two columns named: PID(product ID), and QTY(quantity)

ItemList table contains three columns named: PID(product ID as well), Product, and supID (supplier ID)

Here is the visualization:

Restaurant1:

   PID           QTY
=========   ============
p01           1
p04           5 
p06           4
p10           4          
p12           1
p14           6

Restaurant2:

   PID           QTY
=========   ============
p04           5
p09           4 
p13           3

ItemLIst:

   PID           Product    supID
=========   ============    ======
p01           Beef Steak      mla1        
.             Beef Cutlet     mla2
.             Pork Steak      .
.             Whole Chicken   .
.              .              .
.              .              .
              and so on...    mla15
p15

The supID column isn't relevant for this question since what I want know is how I could combine Restaurant 1 and Restaurant 2 like this:

Restaurant 1 and 2 (combined):

   PID           QTY
=========   ============
p01           1
p04           5 
p06           4
p10           4          
p12           1
p14           6
p04           5
p09           4 
p13           3

In short... I want to literally combine the values of the two table into one table. But I want to use only the JOIN clause such as inner join, outer join, etc.

This is what I've tried that didn't work and I don't know what other solutions I could do.

SELECT ItemList.PID, Restaurant1.QTY, Restaurant2.QTY FROM ItemList
    -> OUTER JOIN Restaurant1 ON ItemList.PID=Restaurant1.PID
    -> OUTER JOIN Restaurant2 ON ItemList.PID=Restaurant2.PID;

I tried different combinations such as using LEFT JOIN and RIGHT JOIN, but it gives me different output.

Hope some could help.

EDIT: I don't want to make another table. Instead, I just want to display it the way I showed it. In which, the combined list of products and quantities from both tables Restaurant1 and Restaurant2 are displayed.

CodePudding user response：

Union the two tables, then sum the quantity, grouping by the product:

select PID, sum(QTY) as QTY
from (
   select PID, QTY from Restaurant1
   union
   select PID, QTY from Restaurant2
   ) as r
group by PID;

CodePudding user response：

You need to UNION ALL both tables to get all rows after that you can sum the quantity up

But it would be simpler to have pone one table with all restaurants and a new column will show which restaurants has the quantoty,.

That is also a good idea because you can only union a certain quantity of taböes

CREATE TABLE restaurant1 (
  `PID` VARCHAR(3),
  `QTY` INTEGER
);

INSERT INTO restaurant1
  (`PID`, `QTY`)
VALUES
  ('p01', '1'),
  ('p04', '5'),
  ('p06', '4'),
  ('p10', '4'),
  ('p12', '1'),
  ('p14', '6');

CREATE TABLE restaurant2 (
  `PID` VARCHAR(3),
  `QTY` INTEGER
);

INSERT INTO restaurant2
  (`PID`, `QTY`)
VALUES
  ('p04', '5'),
  ('p09', '4'),
  ('p13', '3');

SELECT
`PID`, SUM(`QTY`) Qty_both
FROM
(SELECT * FROM restaurant1
UNION ALL
SELECT * FROM restaurant2) t1
GROUP BY `PID`
ORDER BY `PID`

PID | Qty_both
:-- | -------:
p01 |        1
p04 |       10
p06 |        4
p09 |        4
p10 |        4
p12 |        1
p13 |        3
p14 |        6

db<>fiddle here