Click here to Skip to main content
14,691,000 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i want to join two tables in sql.first table name is Shift. Second Table name is Leave. An employee may full present or half day absent or leave, or full day leave. If full day leave no data on shift table. If he give leave letter then record added on leave table. i want to join these two tables.


my tables look like
Shift
Empnum punchdate  shift 
Test1  01/09/2020   1
Test1  03/09/2020   0.5

Leave
Empnum LeaveDate  leaveFN  LeaveAF
Test1  02/09/2020  0.5       0.5
Test1  03/09/2020  0         0.5


i want to join like this
Test1 01/09/2020 1    0    0
Test1 02/09/2020 0    0.5  0.5
Test1 03/09/2020 0.5  0    0.5


What I have tried:

i tried left join, full outer join, cross etc
that shows,
Test1 01/09/2020 1 0.5  0.5
Test1 02/09/2020 1 0.5  0.5
Test1 01/09/2020 1 0    0.5
Test1 02/09/2020 1 0    0.5
Posted
Updated 29-Sep-20 0:01am
v3

1 solution

You didn't show us what query you did use. Nevertheless...

To get desired result, you have to get all distinct dates and EmpNum's from both tables. See:
SET DATEFORMAT dmy;

CREATE TABLE Shift
(
  Empnum varchar(50),
  punchdate date,
  shift decimal(5,1)
);

CREATE TABLE Leave
(
  Empnum varchar(50),
  LeaveDate date,
  leaveFN decimal(5,1),
  LeaveAF decimal(5,1)
);

INSERT INTO Shift (Empnum, punchdate, shift)
VALUES('Test1', '01/09/2020', 1),
('Test1', '03/09/2020', 0.5),
('Test2', '01/09/2020', 1),
('Test2', '02/09/2020', 1)

INSERT INTO Leave(Empnum, LeaveDate, leaveFN, LeaveAF)
VALUES('Test1' , '02/09/2020', 0.5, 0.5),
('Test1', '03/09/2020', 0, 0.5)

;WITH CTE AS
(
    SELECT punchdate AS CurrentDate
    FROM Shift
    UNION ALL
    SELECT LeaveDate As CurrentDate
    FROM Leave
), Emps AS
(
    SELECT EmpNum 
    FROM Shift
    UNION ALL
    SELECT EmpNum
    FROM Leave
)
SELECT c.Empnum, c.CurrentDate, 
  COALESCE(s.shift, 0) shift,
  COALESCE(l.leaveFN, 0) leaveFN,  
  COALESCE(l.leaveAF, 0) leaveAF 
FROM
(
  SELECT DISTINCT cc.CurrentDate, ee.EmpNum
  FROM CTE cc CROSS JOIN Emps ee
) c LEFT JOIN Shift s ON c.CurrentDate = s.punchdate AND c.EmpNum = s.EmpNum
  LEFT JOIN Leave l ON c.CurrentDate = l.LeaveDate AND c.EmpNum = l.EmpNum


Result:
Empnum 	CurrentDate 	shift 	leaveFN 	leaveAF
Test1 	2020-09-01 	1.0 	0.0 	0.0
Test2 	2020-09-01 	1.0 	0.0 	0.0
Test1 	2020-09-02 	0.0 	0.5 	0.5
Test2 	2020-09-02 	1.0 	0.0 	0.0
Test1 	2020-09-03 	0.5 	0.0 	0.5
Test2 	2020-09-03 	0.0 	0.0 	0.0 <- missing record for Test2, which means no corresponding data in Shift and Leave table for 03/09/2020


db<>fiddle[^]

Good luck!
   
v4
Comments
BOOPATHI A 29-Sep-20 6:39am
   
if i add empnum Test2 in shift table, not in leave table now Test1 leave join with Test2 also. how to ignore this.
Maciej Los 29-Sep-20 7:03am
   
In this case a small modification is needed ;) See updated answer.
BOOPATHI A 29-Sep-20 8:01am
   
Great. Working fine. Thank you Maciej Los.
Maciej Los 29-Sep-20 9:28am
   
You're very welcome. To close this thread, use green button, which will mark this answer as a solution.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900