Getting first two unique records from two table joined – Mysql

Table 1: employee_detail:

id              name
1               ABC
2               CCC 
3               FFF 
4               ggg
5               jjj

Table 2: performance_appraisal

id          date_of_join        isAppraisalcomplete         emp_id
1           1-07-2010           Yes                         1
2           09-6-2010           Yes                         2
3           10-7-2012           Yes                         3   
4           23-8-2015           No                          4
5           07-11-2018          No                          5

Table 3: financial_details

id          salary          hike        emp_id          p_a_id          year
1           11000           12          1               1               2016
2           11000           9           1               1               2017
3           11000           11          1               1               2016
4           11000           10          1               1               2017
2           33000           15          2               2               2016
3           36000           10          2               2               2017
4           31000           15          2               2               2016    
5           44001           10          2               2               2017
..........
..........
..........

Expecting Output :

Emp_id      Date_of_join        isAppraisalcomplete         Salary      Hike    year        
1           1-07-2010           Yes                         11000       12      2016
1           1-07-2010           Yes                         11000       9       2017
2           09-06-2010          Yes                         33000       15      2016
2           09-06-2010          Yes                         36000       10      2017
..........
..............

Query i used for :

select * from financial_details  bsd inner join performance_appraisal fi on fi.emp_id = bsd.emp_id limit 2;

My Result :

Emp_id      Date_of_join        isAppraisalcomplete         Salary      Hike        year    
1           1-07-2010           Yes                         11000       12          2016
1           1-07-2010           Yes                         11000       9           2017

On increasing limit it shows all records from emp_id say emp_id 1 not only the first two records but also all records.

How to get first two records from table with joining another table using MySql.

On using where condition, order by desc with limit 2 I get exact result for one record (one employee). But actually i was trying to get all records(all employees) with first two unique data from financial_details table and performance_appraisal table join. Pls help on it.

EDIT:

Query:

CREATE TABLE employee_detail
(
id int,
name varchar(255)
);
CREATE TABLE performance_appraisal
(
id int,
date_of_join varchar(255),
isAppraisalcomplete varchar(255),
emp_id int
);
CREATE TABLE financial_details
(
id int,
salary varchar(255),
hike varchar(255),
emp_id int,
p_a_id int,
t_year varchar(255)
);

insert into employee_detail (id, name) values (1,"abc");
insert into employee_detail (id, name) values (2,"def");
insert into employee_detail (id, name) values (3,"ghi");
insert into performance_appraisal (id, date_of_join, isAppraisalcomplete, emp_id) values (1, "1-07-2010", "Yes", 1);
insert into performance_appraisal (id, date_of_join, isAppraisalcomplete, emp_id) values (2, "09-6-2010", "Yes", 2);
insert into performance_appraisal (id, date_of_join, isAppraisalcomplete, emp_id) values (3, "10-7-2012", "Yes", 3);
insert into performance_appraisal (id, date_of_join, isAppraisalcomplete, emp_id) values (4, "23-8-2015", "No", 4);
insert into performance_appraisal (id, date_of_join, isAppraisalcomplete, emp_id) values (5, "07-11-2018", "No", 5);

insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (1, "11000", "12", 1,1,"2016");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (2, "12000", "9", 1,1,"2017");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (3, "10500", "11", 1,1,"2016");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (4, "11400", "10", 1,1,"2017");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (5, "36000", "15", 2,2,"2016");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (6, "36000", "15", 2,2,"2017");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (7, "31000", "15", 2,2,"2016");
insert into financial_details (id, salary, hike, emp_id,p_a_id, t_year) values (8, "44000", "15", 2,2,"2017");

Answer

Here’s something to think about, although, as written, this solution is exclusively for versions pre-8.0…

SELECT emp_id
     , id 
  FROM 
     ( SELECT emp_id
            , id
            , CASE WHEN @prev = emp_id THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev:=emp_id prev 
         FROM financial_details
            , (SELECT @prev:=null,@i:=0) vars 
        ORDER 
           BY emp_id
            , id
     ) x 
 WHERE i <= 2;
+--------+------+
| emp_id | id   |
+--------+------+
|      1 |    1 |
|      1 |    2 |
|      2 |    5 |
|      2 |    6 |
+--------+------+

Advertisement

Answer