Ignore duplicate rows in a join - and take only one of them

Question

In MySQL I have tables, simplified as follows:

companies (id, name, rank, trade_sector_id)
orders (id, company_id, paid, subtotal)
trade_sectors (id, label)
companies_notes (id, company_id, notes)

I want a single row for each company containing:

• Name of company
• Number of orders
• Total of all subtotals
• Company notes

To simplify it here I am selecting just one company, with id=14401. It has 68 orders:

SELECT 
  companies.id AS company_id, 
  companies.account_name, 
  COUNT(orders.id) AS numSales,
  SUM(orders.`subtotal`) AS subtotal,   
  MAX(trade_sectors.label) AS trade_sector,
  MAX(companies_notes.`notes`) AS notes

FROM companies
  LEFT JOIN `orders` ON (companies.id = orders.`company_id` AND orders.`paid` = 1)
  LEFT JOIN `trade_sectors` ON (companies.trade_sector_id = trade_sectors.`id`)
  LEFT JOIN `companies_notes` ON (`companies_notes`.`company_id` = companies.id)

  WHERE companies.id = '14401'
  GROUP BY companies.id
  ORDER BY companies.rank DESC;

The problem

There are 68 orders for this company but I am getting numSales as 136 (so 2x the number), and also the subtotal is 2x bigger than it should be.

But if I remove the join for NOTES it is correct:

SELECT 
  companies.id AS company_id, 
  companies.account_name, 
  COUNT(orders.id) AS numSales,
  SUM(orders.`subtotal`) AS subtotal,   
  MAX(trade_sectors.label) AS trade_sector

FROM companies
  LEFT JOIN `orders` ON (companies.id = orders.`company_id` AND orders.`paid` = 1)
  LEFT JOIN `trade_sectors` ON (companies.trade_sector_id = trade_sectors.`id`)

  WHERE companies.id = '14401'
  GROUP BY companies.id
  ORDER BY companies.rank DESC;

So it seems the notes join is giving me 2 rows per order. Yes there ARE two notes rows for this company (there should only be 1), but this is not enforced technically. I thought that by using MAX aggregating function on companies_notes.notes only one would be considered. In fact the Group BY clause requires the columns to be aggregated.

How can I prevent the join creating duplicate records that affect the SUM() and MAX() values?

Answer 1

Making companies_notes.company_id unique per your comment, will certainly solve the problem (once you've removed or merged the extra entries to allow the constraint to be applied).

But in the meantime, what you need to do is preprocess companies_notes to ensure there is only one entry per company_id, before attempting to join onto it.

There are various techniques using a nested query/derived table.

What I usually do is apply a row-number, and then filter to the first row. This requires nesting the queries two levels deep, but it means at the outer level you gain access to all the fields from the selected row.

You could possibly also group-by company_id, and then select a MAX (or another suitable aggregate, like GROUP_CONCAT) on the fields you want to be available at the outer level.

To be clear, both of these solutions are applied inside a nested query. You then join onto this nested query. I write mainly in TSQL so apologies if the following syntax is not quite right, but it shows the general idea with a nested query:

SELECT 
  companies.id AS company_id, 
  ...
  MAX(companies_notes.`notes`) AS notes

FROM 
    companies
...

LEFT JOIN (
    SELECT company_id, MAX(notes) AS notes
    FROM companies_notes
    GROUP BY company_id
) AS `companies_notes` ON (`companies_notes`.`company_id` = companies.id)

.... (the rest of your original query)

Answer 2

I thought that by using MAX aggregating function on companies_notes.notes only one would be considered...but I still don't understand why the aggregating function MAX doesn't resolve it

Aggregate functions are applied after the join and grouping occurs and only apply to the column you're aggregating. So the general order of events is the data is duplicated from the join, then it's grouped, and then the aggregate function looks at the rows in the grouping and then applies it's function to those rows, for only that column.

So for MAX(companies_notes.'notes') it looks at the two notes per customer in the grouping and picks the last one (sorted lexicographically). For COUNT(orders.id) it similarly looks at all of the rows within the grouping (136 of them because of the join to the notes table) and then counts them. That's why you still end up with 136 as the numSales. Each column's aggregate function is independent of each other.