Getting first two unique records from two table joined - Mysql

Related searches

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");

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 |
+--------+------+

MYSQL Multiple Join Query to Display Unique Column Values in , I use a LEFT join to still get records of properties that don't have any recommended providers CREATE TABLE properties ( id integer PRIMARY KEY, property INSERT INTO properties VALUES (1, 'property 1'), (2, 'property 2' ) FROM properties p LEFT JOIN properties_recommended_providers pr ON� The sales table contains multiple records for each ItemID - one for each SaleWeek. I want to select all items sold by joining the two tables like so: SELECT items.ItemName, items.ItemId FROM items JOIN sales ON items.ItemId = sales.ItemId WHERE sales.StoreID = ? ORDER BY sales.SaleWeek DESC;

You can use window functions in MySQL 8+:

select *
from financial_details bsd inner join
     (select fi.*,
             row_number() over (partition by emp_id order by id) as seqnum
      from performance_appraisal fi
     ) fi
     on fi.emp_id = bsd.emp_id and seqnum <= 2;

How to Join Two Tables in MySQL, They are used to join two or more different tables on a point in which both the Simple enter your email address and get the download link in your Inbox. consists of records which combines each row from the first table with� This table has four rows. It has the same first two rows as the inner join, but it has two additional rows — rows that are in the Product table on the left but not in the Color table. Notice that the columns from the table Color are blank for the last two rows. And, on the third hand, suppose that you do a right outer join with the following

Thanks to Strawberry Answer which we need to add in where clause for my output.

select * from financial_details bsd inner join performance_appraisal fi on fi.emp_id = bsd.emp_id where bsd.id in (SELECT  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);

Using MySQL DISTINCT to Eliminate Duplicates, In this tutorial, you will learn how to use MySQL DISTINCT clause in the SELECT statement to When querying data from a table, you may get duplicate rows. First, query the last names of employees from the employees table using the following SELECT statement: MySQL without DISTINCT clause on multiple columns� The inner join clause compares each row from the first table with every row from the second table. If values in both rows cause the join condition evaluates to true, the inner join clause creates a new row whose column contains all columns of the two rows from both tables and include this new row in the final result set.

MySQL UNION: Combining Results of Two or More Queries, To combine result set of two or more queries using the UNION operator, these are the First, the number and the orders of columns that appear in all SELECT The following statement combines result sets returned from t1 and t2 tables: 2 and 3 are duplicates, the UNION removed them and kept only unique values. The UNION keyword will return unique records on the result list. When specifying ALL will keep duplicates on the result set, which the OP don't want. SELECT city FROM tableA UNION SELECT city FROM tableB UNION SELECT city FROM tableC SQLFiddle Demo; RESULT

SQL: Distinct values from two tables, SQL: Distinct values from two tables was first published on May 16, 2005. Comments. can u tell me the query in sql server 2000 to get sixth maximum salary AMOUNT FROM EMPLOYEE INNER JOIN SALARY_DETAILS ON EMPLOYEE. If you want a particular first occurrence, sort first, then apply the group-by cheat: select * from ( -- order by the "time" column descending to get the "most recent" row select * from mytable order by time desc ) x group by cid

Down load the SQL DUMP of this student table Distinct records linking two tables. We will create two tables. One is our customer details and other one is with sales details. Same customer may buy more than once so there will be multiple entry for customer in our sales table. We will try to get the name of the customer who has purchased any product.

Comments
  • Hi @Strawberry pls see this link. db-fiddle.com/#&togetherjs=wFu5SlHtVN
  • Thanks i have added in question
  • Sorry - remembering that rows in an RDBMS represent unordered sets, can you define 'first two'
  • Sorry I dont understand
  • Hi thank u very much. it helped for one table, but on using join how could i add another table performance_appraisal for these datas.
  • Go on. Try something.
  • Hi thanks, I m getting error in syntax as missing closing parenthesis in over. Using XAMMP, My Mysql version is innodb_version 5.6.28-76.1, version 10.1.13-MariaDB. How can i solve for older version?