clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591678 fiddles created (45712 in the last week).

select version();
version()
5.7.27
 hidden batch(es)


create table jobs( jobid int(7), datein date, total numeric(5,2), chargeto int(5) );
 hidden batch(es)


create table payments( payid int(7), paymentamount numeric(5,2), paymentjobno int(5), paymentdate date, paymenttype int(1) );
 hidden batch(es)


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);
 hidden batch(es)


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);
 hidden batch(es)


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;
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
 hidden batch(es)


1000, 100, 175 1001, 50, 175 1002, 25, 175
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
 hidden batch(es)