MYSQL Joining Related Tables With Different Row Amounts Per "Record"

Question

Table A is an Orders table with 1 row per order. Table B is an Items table with 1 row per item. They are linked by an order number. When I join them on this O/N, I get multiple lines of Orders if there was more than 1 item on the order. I am grouping by a field in a different table so I cannot use that method.

What is the correct method of joining them so 1 Order row and 3 Item rows doesn't turn into 3 Order and 3 Item?

Apologies for the basic question, my brain is on a meltdown today.

I have simplified what I am asking with the below.

CREATE TABLE Orders
    (`ORDURN` varchar(8), `ORDSOU` varchar(10), `ORDVAL` decimal(9,2))
;

INSERT INTO Orders
    (`ORDURN`, `ORDSOU`, `ORDVAL`)
VALUES
    ('12345112', 'WEB', '28.41'),
    ('22544548', 'ADVERT', '11.58'),
    ('44848643', 'TELEPHONE', '41.18')
;


CREATE TABLE Items
    (`ITMSTK` varchar(10), `ITMQTY` varchar (3), `ITMCOG` int(9), `ITMURN` varchar(8), `ITMSOU` varchar(10))
;

INSERT INTO Items
    (`ITMSTK`, `ITMQTY`, `ITMCOG`, `ITMURN`, `ITMSOU`)
VALUES
    ('WN778', '1', '2.00', '12345112', 'WEB'),
    ('WN776', '1', '1.45', '12345112', 'WEB'),
    ('WN771', '1', '1.86', '12345112', 'WEB'),
    ('WN845', '1', '1.45', '22544548', 'ADVERT'),
    ('WN846', '1', '1.38', '22544548', 'ADVERT'),
    ('WN845', '1', '20.16', '44848643', 'TELEPHONE')
;
CREATE TABLE Sources
    (`SOUCOD` varchar(10), `SOUDESC` varchar(45))
;
INSERT INTO Sources 
    (`SOUCOD`, `SOUDESC`)
VALUES
    ('WEB', 'Web Orders 2016'),
    ('ADVERT', 'Advert Junes 2016'),
    ('TELEPHONE', 'Telephone Orders 2016')
;

And I am then running this query below

    select
S.soucod as Sources,
s.soudesc as Description,
sum(i.itmcog) as COG,
count(DISTINCT o.ordurn) as Orders,
sum(o.ordval) as OrderValue
from sources s
join orders o on o.ordsou = s.soucod
join items i on i.itmsou = s.soucod
group by s.soucod

To give me

Sources     Description           COG   Orders  OrderValue  
ADVERT      Advert Junes 2016       2        1       23.16
TELEPHONE   Telephone Orders 2016  20        1       41.18
WEB         Web Orders 2016         5        1       85.23

Obviously the order values have been skewed by the join as it has counted multiple lines

This is the desired result (Cost of goods is wrong, but I know why that is, just the way I created the data, this column isn't here for any purpose other than to show the need to bring in the Items table):

Sources     Description           COG   Orders  OrderValue  
ADVERT      Advert Junes 2016       2        1       11.58
TELEPHONE   Telephone Orders 2016  20        1       41.18
WEB         Web Orders 2016         5        1       28.41

I hope this explains it.

Mike · Accepted Answer · 2016-08-25T15:39:37.053

Need sequential join with calculate intermediate result. On outer level of join use max() function on result of subquery.

select Sources, Description,
       sum(i.itmcog) as COG,
       max(Orders) as Orders, max(OrderValue) as OrderValue
  from (
        select s.soucod as Sources, s.soudesc as Description, 
               count(o.ordurn) as Orders,
               sum(o.ordval) as OrderValue
          from sources s
          join orders o on o.ordsou = s.soucod
         group by s.soucod
       ) A
   join items i on i.itmsou = A.Sources
  group by A.Sources

If select only one result from depended table you can use subquery in select list:

select s.SOUCOD as Sources, s.soudesc as Description, 
       (select sum(i.itmcog)
          from items i
         where i.itmsou=s.soucod
       ) as COG,
       count(o.ordurn) as Orders,
       sum(o.ordval) as OrderValue
  from sources s
  join orders o on o.ordsou = s.soucod
 group by s.soucod

score 0 · Answer 2 · answered Aug 25 '16 at 16:01

SELECT s.soucod sources
     , s.soudesc description
     , SUM(i.itmcog) COG
     , COUNT(DISTINCT o.ordurn) orders
     , o.ordval ordervalue
  FROM sources s 
  JOIN orders o 
    ON o.ordsou = s.soucod 
  JOIN items i 
    ON i.itmsou = s.soucod
 GROUP 
    BY sources
     , description
     , ordervalue;

MYSQL Joining Related Tables With Different Row Amounts Per "Record"

2 Answers2

Linked