I have a table of job payments, and when matching to transactions need to match up payments on the same day for the same customer that equal a single transaction for that day. I have been able to match, but only return the aggregated total, not each row.
I understand a join will be required but haven't been able to get it right.
DB:
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 (jobid, total) values (1000, 100, 4);
insert into jobs (jobid, total) values (1001, 50, 4);
insert into jobs (jobid, total) values (1002, 25, 4);
insert into jobs (jobid, total) values (1003, 220, 6);
insert into jobs (jobid, total) values (1004, 200, 7);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 100, 1000, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (2, 50, 1001, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (3, 25, 1002, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (4, 220, 1003, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (5, 200, 1004, 01/10/2019, 1);
Code I have:
$groupmatches = $con->query("
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;
");
It is matching and returning a single line with a count value of how many lines added up to a match, but I can't return each of those lines. What am I doing wrong?
Best Answer
I already (tried) to answer this question here, but I'm not deleting it so that my (admittedly sometimes garbled) thought processes are apparent and to remind me always to read the question!
So, using the data in the OP's fiddle here (a comment to my first answer), I first ran the SQL below - my own fiddle for this (final) answer is here. It works in MySQL versions 5.6, 5.7 and 8:
Result:
Then, I had to join this with the payments for each payment per charge type.
Desired result - only want ct = 4
ct = 4 comes from:
I changed this to
IN (1, 2, 4)
- more readable.Result:
The
desired
result is thecsv
column, but as far as I'm concerned thedesirable
result is in the first three separate fields - any type of .csv field is IMHO a bad idea when using SQL - see my previous answer (the bits about GROUP_CONCAT, STRING_AGG & LIST_AGG).