Here is a query which join another table.

I need to get the total amount of invoices where a text string occurs

invoice can have many invoice_product

SELECT count(i.id)
FROM `invoice` i
INNER JOIN `invoice_product` ip ON ip.invoice_id=i.id
WHERE i.block_id=1 && (i.txt LIKE '%str%' || ip.name LIKE '%str%' || ip.txt LIKE '%str%')
  • are you getting any error message? – McNets Mar 23 '17 at 15:12
  • no.. but wrong result – clarkk Mar 23 '17 at 15:13

You need to count distinct ids to get the number of invoices, otherwise you get the total number of rows (after joining). Also you want to aggregate over all invoices (so you don't need a group by):

SELECT count(distinct i.id)
FROM `invoice` i
INNER JOIN `invoice_product` ip ON ip.invoice_id = i.id
WHERE i.block_id = 1 
  AND (i.txt LIKE '%str%' OR ip.name LIKE '%str%' OR ip.txt LIKE '%str%');
| improve this answer | |
  • This has a performance issue due to "Inflate (JOIN), then Deflate (DISTINCT or GROUP BY)". – Rick James Mar 24 '17 at 2:29

This is likely to run faster, especially as the tables grow:

FROM `invoice` i
WHERE block_id = 1 
  AND (txt LIKE '%str%'
       OR EXISTS ( SELECT * FROM `invoice_product` 
                     WHERE invoice_id = i.id
                       AND ( name LIKE '%str%'
                          OR txt LIKE '%str%' )
      )          );
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.