MySQL selects rows where its columns are equal
I have the following tables:
AND:
+----+-----------+-------+----------+
| ID | PaymentID | Price | Quantity |
+----+-----------+-------+----------+
| 1 | 1 | 128 | 1 |
| 2 | 2 | 10 | 2 |
| 3 | 2 | 11 | 1 |
| 4 | 3 | 100 | 2 |
+----+-----------+-------+----------+
IN:
+-----------+------------+
| PaymentID | TotalPrice |
+-----------+------------+
| 1 | 128 |
| 2 | 31 |
| 3 | 201 |
+-----------+------------+
And the request:
SELECT a.ID
FROM a
LEFT JOIN b ON b.PaymentID = a.PaymentID
WHERE b.TotalPrice = (a.Price * a.Quantity)
It works great when a.PaymentID is unique, but some transactions in table A are split and paid (table B) together. The query above returns a.ID = 1, but I need to return a.ID = 1,2,3.
a.PaymentID(1): 128 * 1 = 128 MATCH
a.PaymentID(2): 10 * 2 + 11 * 1 = 31 MATCH
a.PaymentID(3): 100 * 2 = 200 NOT MATCH
+3
source to share
2 answers
You are trying to concatenate the sum of the price and the sum from table a to table b along with PaymentId and use it in a join condition that will be calculated for each row that is not based on an aggregate. You may need to find the aggregate part first and then join with something like
select
a.ID
from a
left join (
select sum(Price*Quantity) as tot,PaymentID
from a group by PaymentID
)x on x.PaymentID = a.PaymentID
join b on b.PaymentID = a.PaymentID and x.tot = b.TotalPrice
+3
source to share
Try the following statement:
SELECT a.ID, b.totalprice
FROM a
LEFT JOIN b ON b.PaymentID = a.PaymentID
group by b.paymentID
having TotalPrice = sum(a.Price * a.Quantity)
UPDATE: after clarification:
select a.id from a where paymentId in(
select paymentID from(
SELECT a.paymentID as paymentID, b.totalprice
FROM a
LEFT JOIN b ON b.PaymentID = a.PaymentID
group by b.paymentID
having TotalPrice = sum(a.Price * a.Quantity)) as c )
+3
source to share