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

2 votes
2 answers
2088 views
                          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?

                        
Asked by Ade (123 rep)
Sep 8, 2023, 05:03 PM
Last activity: Sep 8, 2023, 07:21 PM
Ignore duplicate rows in a join - and take only one of them

Related Questions
