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
    Commented Mar 23, 2017 at 15:12
  • no.. but wrong result
    – clarkk
    Commented Mar 23, 2017 at 15:13

2 Answers 2


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%');
  • This has a performance issue due to "Inflate (JOIN), then Deflate (DISTINCT or GROUP BY)".
    – Rick James
    Commented Mar 24, 2017 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%' )
      )          );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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