I am looking to separate the sums from different vendors on purchase orders with same item number with a group by.

I have 5 tables. I have included my current query below and tables I am using.

Every time I use two inner joins I get duplicate values showing and when I use a sub query, I get an error saying I cannot have more than one value return from a sub query.

CREATE TABLE stock(
    stocknumber     VARCHAR(30),    
    location        VARCHAR(30)
);
INSERT INTO stock VALUES
('22-2552',     'A1');

SELECT * FROM stock

CREATE TABLE head(
    location        VARCHAR(30),
    dateordered     DATE,
    ponumber        INT
);

INSERT INTO head VALUES
('A1',  '10/14/2022',   1),
('A1',  '10/14/2022',   2),
('A1',  '10/14/2022',   3),
('A1',  '10/14/2022',   4);

SELECT * FROM head;

CREATE TABLE details(
    stocknumber         VARCHAR(30),
    quantityordered     INT,
    vendornumber        INT,
    ponumber            INT
);
INSERT INTO details VALUES
('22-2552',     3,  15,     1),
('22-2552',     2,  20,     2),
('22-2552',     1,  15,     3),
('22-2552',     4,  20,     4);

SELECT * FROM details;

CREATE TABLE head1(
    location        VARCHAR(30),
    dateordered     DATE,
    ponumber        INT
);

INSERT INTO head1 VALUES
('A1',  '10/14/2022',   5),
('A1',  '10/14/2022',   6),
('A1',  '10/14/2022',   7),
('A1',  '10/14/2022',   8);

SELECT * FROM head1;

CREATE TABLE details1(
    stocknumber         VARCHAR(30),
    quantityordered     INT,
    vendornumber        INT,
    ponumber            INT
);
INSERT INTO details1 VALUES
('22-2552',     7,  15,     5),
('22-2552',     6,  20,     6),
('22-2552',     5,  15,     7),
('22-2552',     10,     20,     8);

SELECT * FROM details1;

SELECT ST.stocknumber,
       ST.location, 
       PH2.vendornumber AS PHVN,

       PO2.vendornumber AS POVN,
       SUM(PH2.quantityordered) AS phOrderQty,
       SUM(PO2.quantityordered) AS poOrderQty
FROM       stock ST
  
INNER JOIN head PH1
        ON PH1.location = ST.location
INNER JOIN details PH2
        ON ST.stocknumber = PH2.stocknumber
       AND PH1.ponumber = PH2.ponumber

INNER JOIN head1 PO1
        ON PO1.location = ST.location
INNER JOIN details1 PO2
        ON ST.stocknumber = PO2.stocknumber
       AND PO1.ponumber = PO2.ponumber

  
WHERE ST.stocknumber IN ('22-2552', 'JW00', 'JS20FT', 'JW090') 
  AND PH1.location = 'A1'
GROUP BY ST.stocknumber,
         ST.location,
         PH2.vendornumber,

         PO2.vendornumber
       

I keep getting back

What I should be getting back is

CodePudding user response：

You don't want to JOIN your PO orders with your PH orders, you want to UNION them.

(Even better would be to have them in the same table, your data structure appears broken, as is indicated by the need to union them together.)

WITH
  all_orders AS
(
  SELECT
    h.location,
    h.ponumber,
    d.stocknumber,
    d.vendornumber,
    d.quantityordered AS ph_qty,
    0                 AS po_qty
  FROM
    head      AS h
  INNER JOIN
    details   AS d
      ON d.ponumber = h.ponumber

  UNION ALL

  SELECT
    h.location,
    h.ponumber,
    d.stocknumber,
    d.vendornumber,
    0                 AS ph_qty,
    d.quantityordered AS po_qty
  FROM
    head1      AS h
  INNER JOIN
    details1   AS d
      ON d.ponumber = h.ponumber
)
SELECT
  s.location,
  s.stocknumber,
  o.vendornumber,
  SUM(o.ph_qty), 
  SUM(o.po_qty)
FROM
  stock        AS s
INNER JOIN
  all_orders   AS o
    ON  o.location    = s.location
    AND o.stocknumber = s.stocknumber
GROUP BY
  s.location,
  s.stocknumber,
  o.vendornumber

https://dbfiddle.uk/lROTPIER

You don't even really need to join on the stock table, as all the columns your asking for are in the other tables already.