I have 4 MySQL tables

1. companies - {id*, company}
2. employees - {id*, fname, lname, id_company*}
3. technologies - {id*, technology}
4. company_technologies - {id_technology*, id_company*}

Field is indexed if it has '*'.

Companies can use multiple technologies and have multiple employees.

What I'm trying to do is write a query that would select all employees and their companies that use ANY of given technologies. The problem is that I have duplicates when I try to do something like this:

SELECT employees.*, companies.* FROM companies LEFT JOIN employees on companies.id = employees.id_company LEFT JOIN company_technologies ON companies.id = company_technologies.id_company WHERE company_technologies.id_technology IN(1,2,3)

If, for example, a company is using technology 1 and 2 - it will appear twice along with all people.

If I add 'GROUP BY companies.id' condition at the end - I only have one employee for each company, whereas I need all of them.

Tables 1, 2 and 4 have several million records, all tables are InnoDB.

  • GROUP BY in MySQL can give pretty random results if you don't use the select columns also in you GROUP BY statement.. see dbasquare.com/2012/05/15/… based on your line "If I add 'GROUP BY companies.id' condition at the end - " ... EXISTS/IN statement would remove duplicates (like ypercube said in his answer +1 by the way) Nov 22, 2013 at 21:13

3 Answers 3


You can convert the join to a semi-join, using either IN or EXISTS:

SELECT employees.*, companies.* 
FROM companies 
  LEFT JOIN employees 
    ON companies.id = employees.id_company 
      ( SELECT 1
        FROM company_technologies 
        WHERE companies.id = company_technologies.id_company 
          AND company_technologies.id_technology IN (1,2,3)
      ) ;

Another way would be to use GROUP BY or DISTINCT in a subquery and then join:

SELECT e.*, c.* 
FROM companies AS c
  LEFT JOIN employees AS e
    ON c.id = e.id_company 
      ( SELECT id_company
        FROM company_technologies
        WHERE id_technology IN (1,2,3)
        GROUP BY id_company
      ) AS ct
    ON c.id = ct.id_company ;

Once you have one or more queries working correctly, you can start worrying about efficiency. I would add 2 composite indexes on the junction table, one on (id_technology,id_company) and one on (id_company,id_technology) (and remove the single indexes.)

Then test all queries with various parameters and table sizes and check which indexes are used and what are the execution plans.

  • +1 as an side note for Salmon (i assume companies.id and company_technologies.id_company are indexed) correlated subqueries are know to perform badly on MySQL if MySQL can not use the indexing from the outer into the inner subquery.. Nov 22, 2013 at 21:12
  • nice update the derived table would most likely to outperform the correlated subquery (to bad i can only vote one time up) but still... As an side note for Salmon that second query will match e.*, c.* based on company.id FIFO records if company.id is PRIMARY/UNIQUE key as i assume you didnt read this comment because FIFO/LIFO sequence wont be an issue then.. Nov 22, 2013 at 22:34
  • @RaymondNijland Can you explain how "FIFO" and "LIFO" are related to mysql tables? Nov 23, 2013 at 10:39
  • actually i dont know anymore why made that comment on FIFO/LIFO records.. FIFO/LIFO doesnt make any sense with an derived table with WHERE id_technology IN (1,2,3) and GROUP BY because it's always the correct id_company what comes out.. mine explainment i think i serious needed coffee or sleep at that time i wrote the comment.... Nov 23, 2013 at 17:33
  • Both solutions are nice, but I'm running some tests right now - both of them are extremely slow. For example, a query that was taking less than 0.1 seconds with the old query now takes 70 seconds (works perfectly though).
    – Salmon
    Nov 23, 2013 at 20:25

Because you're joining on company_technologies you're going to get a row for each match. Because you're not including anything from company_technologies in your select clause it appears as though you're getting duplicates

What you're looking for is a subquery in your where clause instead of a join in your from clause.

employees.id_company IN 
    (SELECT id_company FROM company_technologies WHERE id_technology IN (1,2,3))
  • Thanks, but whenever I use IN() on these tables, the performance is terrible, to say the least.
    – Salmon
    Nov 22, 2013 at 22:40
  • @Salmon because IN statement in this construct is known to force the MySQL into doing suicidal executions.. these query needs to do an complete nested join.. (number of records not within the where * number of records company_technologies) for an basic estimated calculation what MySQL needs to do your you if you run this kind off qeuries and not even mentioning of the memory temporary table or disk based temporary what proberly is needed so MySQL can process this qeury... Nov 22, 2013 at 23:31

MySQL: Removing duplicate columns on LEFT JOIN for 3 tables the code following should work:

 FROM  table1
LEFT JOIN table2 USING (ID_Prep_Text)
LEFT JOIN table3 USING (ID_Picture)
  • How will that solve the question? i.e. how does that remove duplicates?
    – Tom V
    Mar 3, 2016 at 14:11

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.