Finding rows where totals do not match

MariaDB MySQL

Trying to do what seems like a basic query (join) on two tables.

Receipts

Parts

Trying to get the row from the first table when the totals of subtotal of each parts.receipt_id does not equal receipts.id.total.

My butchered attempts include:

SELECT `receipts`.*
FROM `receipts` 
LEFT JOIN ON `receipts`.`id`=`parts`.`receipt_id` 
WHERE SUM(`parts`.`subtotal`) != `receipts`.`total` 
GROUP BY `parts`.`receipt_id`;

Current work around using compare in PHP (very inefficient).

You can use subqueries as long they only return 1 Value (row)

SELECT 
    r.*
FROM
    receipts r
WHERE
    (SELECT 
            SUM(parts.subtotal)
        FROM
            parts
        WHERE
            receipt_id = r.id) != r.total;

This works as you can see.

If your data have another design, pleae provide a changed dbfille

CREATE TABLE receipts (id  int,   `date`date,     supplier varchar(10),   total DECIMAL(10,2))

INSERT INTO receipts VALUES (1,NOW(),'a',100.1),(2,NOW(),'b',200.2)

CREATE TABLE parts (id    int, receipt_id     int , partType varchar(10),     subtotal DECIMAL(10,2))

INSERT INTO parts VALUEs (1,1,'a',20.0),(2,1,'a',20.0),(3,1,'a',20.1),(4,1,'a',20.0),(2,1,'a',20.0),
(6,2,'a',50.0),(7,2,'a',150.0)

SELECT 
    r.*
FROM
    receipts r
WHERE
    (SELECT 
            SUM(parts.subtotal)
        FROM
            parts
        WHERE
            receipt_id = r.id) != r.total;

id | date       | supplier |  total
-: | :--------- | :------- | -----:
 2 | 2021-06-17 | b        | 200.20

 SELECT 
    r.* ,  (SELECT 
            SUM(parts.subtotal)
        FROM
            parts
        WHERE
            receipt_id = r.id) sumsubtotal
 FROM
    receipts r
            

id | date       | supplier |  total | sumsubtotal
-: | :--------- | :------- | -----: | ----------:
 1 | 2021-06-17 | a        | 100.10 |      100.10
 2 | 2021-06-17 | b        | 200.20 |      200.00

db<>fiddle here