MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.7.27


create table jobs(
  jobid int(7),
  datein date,
  total numeric(5,2),
  chargeto int(5)
);
create table payments(
  payid int(7),
  paymentamount numeric(5,2),
  paymentjobno int(5),
  paymentdate date,
  paymenttype int(1)
);

insert into jobs values (1000, '2019-10-01', 100, 4);
insert into jobs values (1001, '2019-10-01', 50, 4);
insert into jobs values (1002, '2019-10-01', 25, 4);
insert into jobs values (1003, '2019-10-01', 220, 6);
insert into jobs values (1004, '2019-10-01', 200, 7);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (2, 50, 1001, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (3, 10, 1002, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (4, 220, 1003, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (5, 100, 1004, '2019-10-01', 1);
-- SELECT * FROM payments;
SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
FROM payments p
-- WHERE paymenttype IN (1, 2, 4) -- vary the WHERE clause as necessary
-- AND p.paymentdate = '2019-10-01' -- add as many predicates as you like!
GROUP BY p.paymentjobno;
  
-- Now I have both the jobid **and** the SUM, so now can join to payments on 
-- both the jobid **and** that SUM using this as a subquery.


p_jobid
the_sum


1000
100.00

1001
50.00

1002
10.00

1003
220.00

1004
100.00


SELECT j.*, p.*  -- choose your fields as required!
FROM jobs j 
JOIN
(
  SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
  FROM payments p
  -- WHERE paymenttype IN (1, 2, 4) -- vary the WHERE clause as necessary
  -- AND p.paymentdate = '2019-10-01' -- add as many predicates as you like!
  GROUP BY p.paymentjobno) AS t1
  ON j.jobid = t1.p_jobid 
  AND j.total = t1.the_sum
JOIN payments p
ON j.jobid = p.paymentjobno;


jobid
datein
total
chargeto
payid
paymentamount
paymentjobno
paymentdate
paymenttype


1000
2019-10-01
100.00
4
1
25.00
1000
2019-10-01
1

1000
2019-10-01
100.00
4
1
25.00
1000
2019-10-01
1

1000
2019-10-01
100.00
4
1
25.00
1000
2019-10-01
1

1000
2019-10-01
100.00
4
1
25.00
1000
2019-10-01
1

1001
2019-10-01
50.00
4
2
50.00
1001
2019-10-01
1

1003
2019-10-01
220.00
6
4
220.00
1003
2019-10-01
1

jobid	datein	total	chargeto	payid	paymentamount	paymentjobno	paymentdate	paymenttype
1000	2019-10-01	100.00	4	1	25.00	1000	2019-10-01	1
1000	2019-10-01	100.00	4	1	25.00	1000	2019-10-01	1
1000	2019-10-01	100.00	4	1	25.00	1000	2019-10-01	1
1000	2019-10-01	100.00	4	1	25.00	1000	2019-10-01	1
1001	2019-10-01	50.00	4	2	50.00	1001	2019-10-01	1
1003	2019-10-01	220.00	6	4	220.00	1003	2019-10-01	1