June 26, 2012 at 4:41 am
Hi All,
I have two tables, Employee table have master data and Employee_Details have transactional data.it has added EmpID with row number. how to join two table based on EmpID.
Please provide me the Query.
Example Tables DATA:
Employee Table:
EmpID Empname Job
101 xxxxx xxx
102 xxxxx xxx
103 xxxxx xxx
Employee_Details Table:
EmpID salary skills
1011 xxxx xxxx
1012 xxxx xxxx
1013 xxxx xxxx
1021 xxxx xxxx
1022 xxxx xxxx
1023 xxxx xxxx
1024 xxxx xxxx
June 26, 2012 at 5:10 am
select * from Employee_Details ED inner join EmployeeTable ET
on ET.EmpID=ED.EmpID
June 26, 2012 at 5:25 am
Hi Vijayarani,
In my Requirement EmpId in Employee table have 101, same refearence table Employee_Details have 1011 (1012,1013 ) like . how it possible for the below query.
select * from Employee_Details ED inner join EmployeeTable ET
on ET.EmpID=ED.EmpID
June 26, 2012 at 5:36 am
Hi Sambireddy,
Anyhow Employee table will be having the EMP ID which is in EMployee _Details table right. If you need the data from Employee_details only means you can use left/right outer join.
June 26, 2012 at 5:46 am
sambireddy chirra (6/26/2012)
Hi Vijayarani,In my Requirement EmpId in Employee table have 101, same refearence table Employee_Details have 1011 (1012,1013 ) like . how it possible for the below query.
select * from Employee_Details ED inner join EmployeeTable ET
on ET.EmpID=ED.EmpID
You have to tell us what the rules are here, we can only guess.
What does EmpID 101 in the employee table match to in the Employee_Details table? Why are the keys different?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 26, 2012 at 5:53 am
sambireddy chirra (6/26/2012)
Hi All,I have two tables, Employee table have master data and Employee_Details have transactional data.it has added EmpID with row number. how to join two table based on EmpID.
Please provide me the Query.
Example Tables DATA:
Employee Table:
EmpID Empname Job
101 xxxxx xxx
102 xxxxx xxx
103 xxxxx xxx
Employee_Details Table:
EmpID salary skills
1011 xxxx xxxx
1012 xxxx xxxx
1013 xxxx xxxx
1021 xxxx xxxx
1022 xxxx xxxx
1023 xxxx xxxx
1024 xxxx xxxx
Design flaw. EmpID column is not consistent among related tables - you do not do 101 <==> 1011, you do 101 <==> 101
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 26, 2012 at 7:02 am
sambireddy chirra (6/26/2012)
Hi Vijayarani,In my Requirement EmpId in Employee table have 101, same refearence table Employee_Details have 1011 (1012,1013 ) like . how it possible for the below query.
select * from Employee_Details ED inner join EmployeeTable ET
on ET.EmpID=ED.EmpID
It looks like you are using everything after the third digit to indicate which detail row you are desiring. If the ET.EmpID and ED.EmpID are both defined as varchar and NOT NULL, and you were sure that the detail empid was always at least 4 characters, you could use:
select * from Employee_Details ED inner join EmployeeTable ET
on ET.EmpID=substring(ED.EmpID,1,3)
There are enough disadvantages to this that the design shouldn't be used if at all possible. By specifying a substring function on the join criteria, SQL server will not be smart enough to produce efficient joins by using indexes on the employee_details table. If you really must have a row number for employee detail, its best to separate it into its own column, and make sure that the empid on the employeetable directly matches the empid on the employee_details table.
For example, for employee_detail currently numbered as 1011, you'd instead break it into two columns, the 101 goes in one column and the extra 1, the detail line number goes into another column.
June 26, 2012 at 7:29 am
patrickmcginnis59 (6/26/2012)
sambireddy chirra (6/26/2012)
Hi Vijayarani,In my Requirement EmpId in Employee table have 101, same refearence table Employee_Details have 1011 (1012,1013 ) like . how it possible for the below query.
select * from Employee_Details ED inner join EmployeeTable ET
on ET.EmpID=ED.EmpID
It looks like you are using everything after the third digit to indicate which detail row you are desiring. If the ET.EmpID and ED.EmpID are both defined as varchar and NOT NULL, and you were sure that the detail empid was always at least 4 characters, you could use:
select * from Employee_Details ED inner join EmployeeTable ET
on ET.EmpID=substring(ED.EmpID,1,3)
There are enough disadvantages to this that the design shouldn't be used if at all possible. By specifying a substring function on the join criteria, SQL server will not be smart enough to produce efficient joins by using indexes on the employee_details table. If you really must have a row number for employee detail, its best to separate it into its own column, and make sure that the empid on the employeetable directly matches the empid on the employee_details table.
For example, for employee_detail currently numbered as 1011, you'd instead break it into two columns, the 101 goes in one column and the extra 1, the detail line number goes into another column.
Actually, I would make a different assumption. I would incorporate a LEN in the substring to remove only 1 character from the right side. Again, this assumes that there are only the characters 1-9 appended. I was thinking of using row number, but then there is no group by to do it properly.
USE tempdb
GO
CREATE TABLE #employee (EmpID VARCHAR(100), Empname VARCHAR(10), Job VARCHAR(6))
INSERT INTO #employee
SELECT 101, 'xxxxx', 'xxx'
UNION ALL
SELECT 102, 'xxxxx', 'xxx'
CREATE TABLE #Employee_Details (EmpID VARCHAR(100), salary VARCHAR(10), skills VARCHAR(6))
INSERT INTO #Employee_Details
SELECT 1011, 'xxxx', 'xxxx'
UNION ALL
SELECT 1012, 'xxxx', 'xxxx'
UNION ALL
SELECT 1013, 'xxxx', 'xxxx'
UNION ALL
SELECT 1021, 'xxxx', 'xxxx'
UNION ALL
SELECT 1022, 'xxxx', 'xxxx'
UNION ALL
SELECT 1023, 'xxxx', 'xxxx'
UNION ALL
SELECT 1024, 'xxxx', 'xxxx'
SELECT *
FROM #employee e
INNER JOIN #Employee_Details ed
ON e.EmpID = SUBSTRING(ed.EmpID, 1, LEN(ed.EmpID)-1)
DROP TABLE #employee
DROP TABLE #Employee_Details
To the OP, as mentioned, you need to change this table to have a column of empid that exactly matches the empid of the other table. If you don't you will certainly have problems. This is really poor design.
Jared
CE - Microsoft
June 26, 2012 at 7:34 am
Thanks Jared it is working.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply