mySQL - SUM and COUNT and JOIN to display all records

Question

This is a following question to this one Join two tables with SUM and COUNT. What I try to do is to have all values displayed as some are in history table and not in rota table or vice-versa (999 and 777)

So my tables are:

create table history (
    code int(10) primary key,
    PN varchar(10) not null,
    Qty int(10) not null,
    LOC_ID int(10));

insert into history values (1,  'T1', 1, 1);
insert into history values (2,  'A1', 2,2);
insert into history values (3,  'J1', 3,3);
insert into history values (4,  'A2', 1,4);
insert into history values (5,  'J2', 2,1);
insert into history values (6,  'A3', 3,2);
insert into history values (7,  'J3', 4,3);
insert into history values (8,  'T1', 5,4);
insert into history values (9,  'A1', 1,1);
insert into history values (10, '999', 3,2);
insert into history values (11, 'J2', 4,3);
insert into history values (12, 'A1', 3,4);
insert into history values (13, 'J2', 5,1);

create table rota (
        code int(10) primary key,
    PN varchar(10) not null,
    SN varchar(10) not null,
    LOC_ID int(10));

insert into rota values (1,     'T1',   't1a',1);
insert into rota values (2,     'A1',   'a1a',2);
insert into rota values (3,     'J1',   'j1a',3);
insert into rota values (4,     'A2',   'a2a',4);
insert into rota values (5,     'J2',   'j2a',1);
insert into rota values (6,     'A3',   'a3a',2);
insert into rota values (7,     'J3',   'j3a',3);
insert into rota values (8,     '777',   't1b',4);
insert into rota values (9,     'A1',   'a1b',1);
insert into rota values (10,    'J2',   'j2b',2);
insert into rota values (11,    'J2',   'j2c',3);
insert into rota values (12,    'A1',   'a1c',4);
insert into rota values (13,    'J2',   'j2d',1);
insert into rota values (14,    'J2',   'j2e',2);
insert into rota values (15,    'J2',   'j2f',3);

create table loca (
        code1 int(10) primary key,
    LOC varchar(10) not null);

insert into loca values (1,     'AAA');
insert into loca values (2,     'BBB');
insert into loca values (3,     'CCC');
insert into loca values (4,     'DDD');

The code I have got is

select CASE WHEN a.pn IS NULL THEN b.pn ELSE a.pn  END AS PN
, a.q
, b.c 
, a.LOC_ID
, b.LOC_ID

from
(select 
h.pn
, sum(qty) q
, h.LOC_ID
from 
history h

group by h.pn, h.LOC_ID) a
RIGHT JOIN 
(select 
r.pn
, count(sn) c
, r.LOC_ID
from 
rota r
group by r.pn, r.LOC_ID) b
on a.pn = b.pn WHERE a.LOC_ID = b.LOC_ID
order by a.pn;

The above code works great for all PN that are in both tables. The problem is for values that are specific to one of the tables. I can remove the WHERE clause from JOIN but it is not corect. The question is - how to get all PNs from history and rota where some of them are present i just one table. I had some luck with RIGHT JOIN but that did not cover unique values from the other table. Any one came across solution before?

Results shoud look like the following table

 PN      LOC_ID     Count   Qty
 T1        1           1    1
 A1        2           1    2
 J1        3           1    3
 A2        4           1    1
 J2        1           2    2
 A3        2           1    3
 J3        3           1    4
777        4           1    NULL
 A1        1           1    1
 J2        2           2    NULL
 J2        3           2    4
 A1        4           1    3
 J2        1           2    2
 J2        2           2    NULL
 J2        3           2    4
999        2           NULL 3

Sounds suspiciously like `full outer join` (you want all records from both sides of the join), which is not supported by MySQL. But I agree with @scaisEdge , we need to see the expected results. — Shadow, Jul 25 '18 at 11:14
Results added. Please look at PN 777 and 999. If I can list them in my results the rest will follow :) — Kalenji, Jul 25 '18 at 11:15
@P.Salmon - code was simplified. Any idea how to get desired results? — Kalenji, Jul 25 '18 at 11:24
I linked you the duplicate topic that explains how to simulate full outer join in MySQL. — Shadow, Jul 25 '18 at 11:35

Zaynul Abadin Tuhin · Accepted Answer · 2018-07-25T11:38:00.353

1

use another join and that is left and make them union

select t.PN,t.q,t.c,t.LOC_ID,t.LOC_ID_b from 
(
  select CASE WHEN a.pn IS NULL THEN b.pn ELSE a.pn  END AS PN
, a.q
, b.c 
, a.LOC_ID
, b.LOC_ID as LOC_ID_b

from
(select 
h.pn
, sum(qty) q
, h.LOC_ID
from 
history h

group by h.pn, h.LOC_ID) a
RIGHT JOIN 
(select 
r.pn
, count(sn) c
, r.LOC_ID
from 
rota r
group by r.pn, r.LOC_ID) b
on a.pn = b.pn and a.LOC_ID = b.LOC_ID

) as t
union 
select t2.PN,t2.q,t2.c,t2.LOC_ID,t2.LOC_ID_b from
(
  select CASE WHEN a.pn IS NULL THEN b.pn ELSE a.pn  END AS PN
, a.q
, b.c 
, a.LOC_ID
, b.LOC_ID as LOC_ID_b

from
(select 
h.pn
, sum(qty) q
, h.LOC_ID
from 
history h

group by h.pn, h.LOC_ID) a
left JOIN 
(select 
r.pn
, count(sn) c
, r.LOC_ID
from 
rota r
group by r.pn, r.LOC_ID
) b
on a.pn = b.pn and a.LOC_ID = b.LOC_ID
) t2

http://sqlfiddle.com/#!9/c20c81/20

edited Jul 25 '18 at 11:38

answered Jul 25 '18 at 11:18

Zaynul Abadin Tuhin

31,407
5
33
63

Thanks Zaynul...777 and 999 are not present in the results. – Kalenji Jul 25 '18 at 11:21
http://sqlfiddle.com/#!9/c20c81/9 use this link here i all results returned as like your output – Zaynul Abadin Tuhin Jul 25 '18 at 11:22
let me check again – Zaynul Abadin Tuhin Jul 25 '18 at 11:26
check now it works – Zaynul Abadin Tuhin Jul 25 '18 at 11:34
http://sqlfiddle.com/#!9/c20c81/19 – Zaynul Abadin Tuhin Jul 25 '18 at 11:34
@ Zaynul - appreciate your help. Would you please have a look and see why 999 is not listed? – Kalenji Jul 25 '18 at 11:37
http://sqlfiddle.com/#!9/c20c81/20 – Zaynul Abadin Tuhin Jul 25 '18 at 11:38
yes 999 also in listed – Zaynul Abadin Tuhin Jul 25 '18 at 11:38
if you help it please accept my answer – Zaynul Abadin Tuhin Jul 25 '18 at 11:38
@ Zaynul Bravo and well done :) – Kalenji Jul 25 '18 at 11:39
1

That's a big lump of code here's something simpler http://sqlfiddle.com/#!9/a717f5/1 – P.Salmon Jul 25 '18 at 11:46
@P.Salmon impressive! Would you be able to advise if the results can be presented in pivoted table. So each location will get one column for c and one for q? [link]https://stackoverflow.com/questions/51540057/mysql-5-5-pivot-values-to-columns-after-two-tables-linked-with-union – Kalenji Jul 27 '18 at 09:09

mySQL - SUM and COUNT and JOIN to display all records

1 Answers1