# Finding rows where totals do not match

## All we need is an easy explanation of the problem, so here it is.

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

Receipts

id date supplier total

Parts

id receipt_id partType subtotal

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).

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂