Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > select version(); > > <pre> > | version() | > | :-------- | > | 5.7.27 | > </pre> <!-- --> > create table jobs( > jobid int(7), > datein date, > total numeric(5,2), > chargeto int(5) > ); > > <pre> > ✓ > </pre> <!-- --> > create table payments( > payid int(7), > paymentamount numeric(5,2), > paymentjobno int(5), > paymentdate date, > paymenttype int(1) > ); > > > <pre> > ✓ > </pre> <!-- --> > 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); > > <pre> > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > </pre> <!-- --> > insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 100, 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, 25, 1002, '201-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, 200, 1004, '2019-10-01', 1); > > <pre> > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > </pre> <!-- --> > SELECT j1.*, j2.* > FROM jobs AS j1 > JOIN (SELECT jobs.jobid, payments.paymentamount, payments.paymentjobno, COUNT(*) AS count > FROM (jobs LEFT JOIN payments ON payments.paymentjobno = jobs.jobid) > WHERE payments.paymentdate = '$dt' AND (payments.paymenttype = 1 OR payments.paymenttype = 2 OR payments.paymenttype = 4) > GROUP BY jobs.chargeto > HAVING SUM(payments.paymentamount) = '$at' AND COUNT(*) > 1) AS j2 > ON j1.jobid = j2.paymentjobno; > > <pre> > Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fiddle_ZNFJVWNMNMIJXNBIMWYT.jobs.jobid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by > </pre> <!-- --> > 1000, 100, 175 > 1001, 50, 175 > 1002, 25, 175 > > <pre> > You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1000, 100, 175 1001, 50, 175 1002, 25, 175' at line 1 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=26afffc33c2dc00e09362eb21d2e016e)*
back to fiddle