joining two tables and grouping only if two or more records has same value

Question

I have 2 tables:

manifests
id | customer | paid_order | price | payment_method

paid_orders
id | paid

JOIN paid_orders ON manifests.paid_order = paid_order_id

Let's assume this scenario:
There are 2 same customer but has different payment_method.

customer | paid_order | price | payment_method |  paid  |
    1    |      1     | 200   |        0       |  200   |
    1    |     NULL   | 100   |        1       |  NULL  |
    2    |     NULL   | 150   |        1       |  NULL  |

I only want to GROUP BY customer but also PICK only the payment_method of 0 only if there are two same customer.

Expected result:

customer | paid_order | price | payment_method |  paid  |
    1    |      1     | 200   |        0       |  200   |
    2    |     NULL   | 150   |        1       |  NULL  |

@Strawberry I'm not sure this is an _exact_ duplicate here, because there is some business logic which must be assumed to write a query like that. — Tim Biegeleisen, Jul 27 '18 at 06:34
MANY `manifests` records can have same `paid_order` ids. By ignoring `payment_method = 1` we can focus on `customers`. But I don't want to eliminate all the `payment_method = 1`, because I also need the customer from it. — Fransisco Wijaya, Jul 27 '18 at 06:37
MySQL or MariaDB? These are different DBMS. And then, which version? As of MySQL 8.0 or MariaDB 10.2.0 you can rank your records with `ROW_NUMBER`. — Thorsten Kettner, Jul 27 '18 at 06:37
I wanted to post an acording answer, but could not as your request has been marked duplicate unfortunately. — Thorsten Kettner, Jul 27 '18 at 06:41
Tim's answer is also good (but see my comment on it). However, window functions like `ROW_NUMBER` are great, and it's worth reading up on these anyway. — Thorsten Kettner, Jul 27 '18 at 06:43
@ThorstenKettner I'm surprised you don't have a gold medal in MySQL. — Tim Biegeleisen, Jul 27 '18 at 06:49

score 1 · Accepted Answer · answered Jul 27 '18 at 06:33

1

One approach would be to always select the record from one, or possibly more, customer records where the payment_method has the minimum value:

SELECT m1.*
FROM manifest m1
INNER JOIN
(
    SELECT customer, MIN(payment_method) AS min_payment_method
    FROM manifest
    GROUP BY customer
) m2
    ON m1.customer = m2.customer AND m1.payment_method = m2.min_payment_method;

This logic should work, because if a payment_method of zero be present, then this record would be targeted. Otherwise, the one value record would be targeted.

answered Jul 27 '18 at 06:33

Tim Biegeleisen

502,043
27
286
360

Your answer is identical to linked solutions – Strawberry Jul 27 '18 at 06:37
While the answer is good, I would always prefer to have criteria in the `WHERE` clause for the sake of readability: `select * from manifests where (customer, payment_method) in (select customer, min(payment_method) …`. – Thorsten Kettner Jul 27 '18 at 06:39
@ThorstenKettner But what about performance? Anyway, most DBs do not support that tuple syntax. – Tim Biegeleisen Jul 27 '18 at 06:47
@Tim Biegeleisen: You are right mentioning performance. MySQL was kind of notorious for bad performance on `IN` clauses once. I'd hope this is a matter of the past, though. I always strive to get my queries as readable as possible and only alter them when performance issues actually occur. And if the optimizer does a good job here, both queries should result in the same execution plan, of course. – Thorsten Kettner Jul 27 '18 at 06:55

joining two tables and grouping only if two or more records has same value

1 Answers1