I have a query that returns the credit notes (CN) and debit notes (DN) of an operation, each CN is accompanied by two or more DNs, at the time of paging, for example I must bring 10 operations, that is, 10 CN and their respective DNs, but if I leave the limit at 10, it will also count the debit notes of the operation that I must return in the query. In other words, it will only bring me 2, 3 or 4 operations depending on the number of DN that accompany the credit note.
SELECT
value, installment, payment_plan_id, model,
creation_date, operation
FROM payment_plant
WHERE model != 'IMMEDIATE'
AND operation IN ('CN', 'DN')
AND creation_date BETWEEN '2017-06-12' AND '2017-07-12 23:59:59'
ORDER BY
model,
creation_date,
operation
LIMIT 10
OFFSET 1
Example of the table ignoring some fields:
| id | payment_plan_id | value | installment | operation |
---------------------------------------------------------
| 1 | b3cdaede | 12 | 1 | NC |
| 2 | b3cdaede | 3.5 | 1 | ND |
| 3 | b3cdaede | 1.2 | 1 | ND |
| 4 | e1d7f051 | 36 | 1 | NC |
| 5 | e1d7f051 | 5.9 | 1 | ND |
| 6 | 00e6a0b4 | 15 | 1 | NC |
| 7 | 00e6a0b4 | 1 | 1 | ND |
| 8 | 00e6a0b4 | 3.6 | 1 | ND |
How can I limit that Limit so that it only considers CNs?
You can't do that directly. The LIMIT is by records (rows resulting from the query). An alternative that occurs to me is:
That is, first you look for the CN, limited to the corresponding page and you bring their
payment_plan_id
. Then you get all the CN/DN that correspond to thosepayment_plan_id
.Of course, this requires that each effectively
payment_plan_id
correspond to a single CN. And to be efficientpayment_plan_id
it must be indexed.