GROUP BY with join

Question

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

Answer 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%');

Answer 2

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%' )
      )          );