MySQL Join two Tables to get records with limit from child

Question

I have two tables

Technology +--------+--------+ | tid(P) | name | +--------+--------+ | 1 | Java | |--------|--------| | 2 | PHP | +--------+--------+

Employee
+----------+----------+------------+
| eid(P) | tid(F) ^| join_date |
+----------+----------+------------+
|    1 | 1 | 2013-10-01 |
|----------|----------|------------|
|    2 | 1 | 2013-10-10 |
|----------|----------|------------|
|    3 | 1 | 2013-10-12 |
|----------|----------|------------|
|    4 | 1 | 2013-09-10 |
|----------|----------|------------|
|    5 | 1 | 2013-11-10 |
|----------|----------|------------|
|    6 | 1 | 2013-12-10 |
|----------|----------|------------|
|    7 | 2    | 2013-08-01 |
|----------|----------|------------|
|    8 | 2    | 2013-10-28 |
|----------|----------|------------|
|    9 | 2    | 2013-05-12 |
|----------|----------|------------|
|    10 | 2    | 2013-10-10 |
|----------|----------|------------|
|    11 | 2    | 2013-11-10 |
|----------|----------|------------|
|    12 | 2    | 2013-12-05 |
|----------|----------|------------|

I need to get data of recently joined three employees for each technology. I tried different joins and also did google on this but didn't get any success.

Expected Result +-------+--------+-------+------------+ | tid | name | eid | join_date | +-------+--------+-------+------------+ | 1 | Java | 6 | 2013-12-10 | +-------+--------+-------+------------+ | 1 | Java | 5 | 2013-11-10 | +-------+--------+-------+------------+ | 1 | Java | 3 | 2013-10-12 | +-------+--------+-------+------------+ | 2 | PHP | 12 | 2013-12-05 | +-------+--------+-------+------------+ | 2 | PHP | 11 | 2013-11-10 | +-------+--------+-------+------------+ | 2 | PHP | 8 | 2013-10-28 | +-------+--------+-------+------------+

What should be my query?

Please guide.

Thanks,
Ankur

+1 for taking the effort to nicely format your tables, though the fact that the first table doesn't *quite* line up is giving me a few twitches. — Chris Hayes, Dec 28 '13 at 07:28

Saharsh Shah · Accepted Answer · 2013-12-31T12:53:06.180

4

Try this:

SELECT A.tid, A.name, A.eid, A.join_date 
FROM (SELECT IF(@tid = @tid:=t.tid, @cnt:=@cnt+1, @cnt:=0) rowNo, t.tid, t.name, e.eid, e.join_date 
      FROM Technology t 
      INNER JOIN Employee e ON t.tid = e.tid, (SELECT @tid:=0, @cnt:=0) A
      ORDER BY t.tid, e.join_date DESC
     ) AS A
WHERE A.rowNo < 3;

EDIT::

SELECT * FROM(
    SELECT *, IF(@tid=@tid:=result.tid,@count:=@count+1,@count:=0) as pos
    FROM
        (SELECT t.tid, t.name, e.eid, e.join_date
          FROM Technology t
          JOIN Employee e
          WHERE e.tid = t.tid
          ORDER BY t.tid, e.join_date DESC) result
          JOIN (SELECT @tid:=0, @count:=0) c) finalre
where finalre.pos < 3;

edited Dec 31 '13 at 12:53

answered Dec 28 '13 at 06:32

Saharsh Shah

28,687
8
48
83

I think it should be LEFT OUTER JOIN instead of INNER(In question "recently joined three employees for each technology") – Damodaran Dec 28 '13 at 07:00
2

check http://dev.mysql.com/doc/refman/5.0/en/user-variables.html AND Sqlfiddle http://sqlfiddle.com/#!2/794858/1 – Damodaran Dec 28 '13 at 07:04
@Saharsh: With you query i am getting 6 rows only but not getting in correct order. I am trying to get that. Please if you can look into that. – Ankur Raiyani Dec 28 '13 at 07:17
@AnkurRaiyani If you find that you got your answer than `Accept` the answer which is useful to you – Saharsh Shah Dec 30 '13 at 04:38
@SaharshShah I have `Accept` it, Just a suggestion, i think you should edit this answer with below correct answer. So other can get the correct answer. – Ankur Raiyani Dec 31 '13 at 11:59

score 1 · Answer 2 · edited May 23 '17 at 12:34

You can try GROUP_CONCAT and SUBSTRING_INDEX

SELECT T.tid,T.name, 
SUBSTRING_INDEX(GROUP_CONCAT(eid ORDER BY join_date DESC), ',', 3) AS `eid`,
SUBSTRING_INDEX(GROUP_CONCAT(join_date ORDER BY join_date DESC), ',', 3) AS `join_date`
FROM Technology T LEFT OUTER JOIN Employee E
ON E.tid = T.tid
GROUP BY T.tid

Here if you need x entries just change the count variable in SUBSTRING_INDEX to x You need to process the field eid and join_date in your application logic. You can try some string tokenizer.
Here is the SQLFIDDLE

REF: How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?

score 0 · Answer 3 · edited May 23 '17 at 10:32

Finally got the result !!!

Thanks a lot to Saharsh Shah. From his answer, i am able to solve this problem.

SELECT * FROM(
    SELECT *, IF(@tid=@tid:=result.tid,@count:=@count+1,@count:=0) as pos
    FROM
        (SELECT t.tid, t.name, e.eid, e.join_date
          FROM Technology t
          JOIN Employee e
          WHERE e.tid = t.tid
          ORDER BY t.tid, e.join_date DESC) result
          JOIN (SELECT @tid:=0, @count:=0) c) finalre
where finalre.pos < 3;

Please check at SQLFIDDLE

MySQL Join two Tables to get records with limit from child

3 Answers3