MySQL: Two of every set

Question

Assuming I have table "Department" (200 row) & "Employees" (300,000 row) each has their own details And the vector table combining both is "dep_emp"

CREATE TABLE IF NOT EXISTS `dep_emp` (
  `id` int(11) NOT NULL DEFAULT '0',
  `dep_id` int(11) NOT NULL DEFAULT '0',
  `emp_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

My question is how to make a query to get two of employees (any two employees) for every Department? My solution so far is this:

(SELECT * FROM `dep_emp` WHERE dep_id=1 LIMIT 2) UNION ALL
(SELECT * FROM `dep_emp` WHERE dep_id=2 LIMIT 2) UNION ALL
(SELECT * FROM `dep_emp` WHERE dep_id=3 LIMIT 2) ........etc

of course this is done {n} times for each Dept so if I wanted to display two Employees for all dept then I will have to write 199 UNION ALL, this is very ugly Any other solutions PS. there are a lot of details that I have moved out of the problem to make things a lot simpler

Try looking at [http://stackoverflow.com/questions/2643314/mysql-group-by-limit][1] seems like the same issue. [1]: http://stackoverflow.com/questions/2643314/mysql-group-by-limit — Luchian Grigore, Jul 22 '11 at 09:39

Ariel · Answer 1 · 2011-07-22T13:08:20.810

2

This may work:

SELECT *
FROM Department
JOIN dep_emp USING (dep_id)
JOIN Employees USING (emp_id)
WHERE emp_id IN
  (SELECT emp_id
     FROM Employees
     JOIN dep_emp USING (emp_id)
    WHERE dep_emp.dep_id = Department.dep_id LIMIT 2)

I didn't test it though.

OK, that didn't work. Try this instead:

SELECT *
FROM Department
JOIN dep_emp USING (dep_id)
JOIN Employees USING (emp_id)
JOIN (SELECT emp_id, dep_id
        FROM dep_emp
    GROUP BY dep_id
       LIMIT 2) emp_2
  USING (emp_id, dep_id)

BTW don't forget to put some indexes on the tables. On dep_emp put a primary key on both columns AND a unique key on the columns in the reverse order! (That way the index works either way.) (You don't need the ID column in dep_emp BTW, at least not in the structure you showed.)

edited Jul 22 '11 at 13:08

answered Jul 22 '11 at 09:35

Ariel

25,995
5
59
69

the last line would give an error, there is no "Department in subquery Unknown column 'Department.dep_id' in 'where clause' – bhefny Jul 22 '11 at 11:10
@bhefny Did you actually try it and are reporting the error? Because MySQL does support dependent subqueries, which is what this is. The subquery looks to the parent query to find the table Department. – Ariel Jul 22 '11 at 11:28
Yes, I'm actually reporting the error I'm getting after testing. this is the my data: * MySQL client version: 5.1.49 * PHP extension: mysqli * Apache/2.2.16 (Ubuntu) – bhefny Jul 22 '11 at 11:31
@bhefny Try the new query I put in the answer. – Ariel Jul 22 '11 at 13:02

ypercubeᵀᴹ · Answer 2 · 2011-07-22T13:10:07.353

SELECT d.*, e.* 
FROM Department AS d
  JOIN Employees AS e 
    ON e.id IN
         ( SELECT de.emp_id
           FROM dep_emp AS de
           WHERE de.dep_id = d.id
           LIMIT 2
         )

Another, very different way to get two emp_id for every department:

SELECT dep_id
     , MIN(emp_id) AS emp_id_1
     , MAX(emp_id) AS emp_id_2
FROM dep_emp
GROUP BY dep_id

or (to have in separate rows, so this can be joined to the other 2 tables):

    SELECT dep_id
         , MIN(emp_id) AS emp_id
    FROM dep_emp
    GROUP BY dep_id
  UNION ALL
    SELECT dep_id
         , MAX(emp_id)
    FROM dep_emp
    GROUP BY dep_id

The first query does not work in MySQL, because LIMIT is not allowed inside IN subqueries. Here's another approach:

SELECT e.*
FROM
    ( SELECT DISTINCT dep_id
      FROM  dep_emp
    ) AS d
  JOIN
    dep_emp AS e
      ON  d.dep_id = e.dep_id
      AND e.emp_id <=
          COALESCE( ( SELECT de.emp_id
                      FROM dep_emp AS de
                      WHERE de.dep_id = d.dep_id
                      ORDER BY de.emp_id         --- OFFSET 1
                      LIMIT 1 OFFSET 1           --- to get 2 employess
                    )                            --- per department
                  , 9999999 )
ORDER BY e.dep_id
       , e.emp_id

Use OFFSET x to get x+1 employees per department.

The Max Min solution is limited incase I wanted 3 of every row — bhefny, Jul 22 '11 at 11:21

MySQL: Two of every set

2 Answers2

Linked