Hello. Something Friday seems to have an effect.

There is a table of invoices for payment (invoices) and their payments (payments). One invoice for payment can be paid in several payments.

Tables:
invoices - id (int), ...
payments - id (int), invoice_id (int), method (enum Wire, PayPal, Webmoney) ...

You need to get invoices containing for example PayPal, but you need to pick up all payments.
Those.

  SELECT invoices.id AS invoice_id, GROUP_CONCAT(payments.method) AS methods, ... FROM invoices LEFT OUTER JOIN payments ON payments.invoice_id = invoices.id GROUP BY invoices.id HAVING GROUP_CONCAT(payments.method) LIKE '%PayPal%'

As a result, we obtain a table of invoices and by what methods each record is paid.
UPD: add sqlfiddle: http://sqlfiddle.com/#!9/d6a33d/1

Confused by LIKE in the condition of HAVING. How to do without it in this request?

    1 answer 1

    You can try double JOIN to discard invoices with other payments, but you have to add DISTINCT in case one invoice is paid for with several PayPal payments:

     SELECT DISTINCT invoices.id AS invoice_id, GROUP_CONCAT(DISTINCT payments.method ORDER BY payments.method SEPARATOR ', ') AS methods, ... FROM invoices JOIN payments p1 ON p1.invoice_id = invoices.id LEFT OUTER JOIN payments ON payments.invoice_id = invoices.id WHERE p1.method = 'PayPal' GROUP BY invoices.id
    • What can you say about this method? HAVING SUM (payments.method = 'PayPal')> 0 sqlfiddle.com/#!9/d6a33d/14 - Ray
    • A good option. But the question arose that when duplicating names, should they all be displayed in the methods field? - vikolyada
    • not. Methods must have unique methods. But I filter them (uniquely) before outputting to the user, so how it will turn out in the request is not very important. - Ray
    • Then you need to fix it on GROUP_CONCAT(DISTINCT payments.method ORDER BY payments.method SEPARATOR ', ') . sqlfiddle - vikolyada
    • @Ray By the way, if the percentage of payments through PayPal is not particularly large, then the option with the second join may be more effective than any checks in having. If it’s great, it’s the other way around - Mike