Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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) –  Raymond Nijland Nov 22 '13 at 21:13

2 Answers 2

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 
WHERE EXISTS
      ( 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 
  JOIN
      ( 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.

share|improve this answer
    
+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.. –  Raymond Nijland Nov 22 '13 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.. –  Raymond Nijland Nov 22 '13 at 22:34
    
@RaymondNijland Can you explain how "FIFO" and "LIFO" are related to mysql tables? –  ypercube Nov 23 '13 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.... –  Raymond Nijland Nov 23 '13 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 '13 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.

WHERE
employees.id_company IN 
    (SELECT id_company FROM company_technologies WHERE id_technology IN (1,2,3))
share|improve this answer
    
Thanks, but whenever I use IN() on these tables, the performance is terrible, to say the least. –  Salmon Nov 22 '13 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... –  Raymond Nijland Nov 22 '13 at 23:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.