0

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%')
GROUP BY i.id
2
  • are you getting any error message?
    – McNets
    Mar 23, 2017 at 15:12
  • no.. but wrong result
    – clarkk
    Mar 23, 2017 at 15:13

2 Answers 2

1

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

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

SELECT COUNT(*)
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.