0

I have two tables one for storing the dates of mont and other for keeping attendance details of employees

in my condition the attendance_days table has 31 records in employee attendance table one employee have 29 and another employee 8 records total 37 records data as following

create table attendance_days 
(id serial primary key not null, 
attendance_date date );



INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-01');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-02');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-03');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-04');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-05');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-06');

INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-07');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-08');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-09');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-10');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-11');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-12');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-13');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-14');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-15');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-16');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-17');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-18');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-19');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-20');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-21');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-22');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-23');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-24');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-25');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-26');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-27');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-28');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-29');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-30');
INSERT INTO attendance_days  (attendance_date) VALUES ('2017-08-31');`

`

create table  employee_attendance
 (id serial primary key not null, 
 attendance_day_id integer ,
 employee_id integer,
 first_in_time timestamp,
 last_out_time timestamp );


INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 1, 407, '2017-08-01 09:30:00', '2017-08-01 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES (2, 407, '2017-08-02 09:30:00', '2017-08-02 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 3, 407, '2017-08-03 09:30:00', '2017-08-03 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES (4, 407, '2017-08-04 09:30:00', '2017-08-04 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 5, 407, '2017-08-05 09:30:00', '2017-08-05 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 6, 407, '2017-08-06 09:30:00', '2017-08-06 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 7, 407, '2017-08-07 09:30:00', '2017-08-07 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 8, 407, '2017-08-08 09:30:00', '2017-08-08 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 9, 407, '2017-08-09 09:30:00', '2017-08-09 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 12, 407, '2017-08-12 09:30:00', '2017-08-12 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 13, 407, '2017-08-13 09:30:00', '2017-08-13 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 14, 407, '2017-08-14 09:30:00', '2017-08-14 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES (15, 601, '2017-08-15 10:44:40', '2017-08-15 10:47:36');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 15, 407, '2017-08-15 09:30:00', '2017-08-15 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 16, 407, '2017-08-16 09:30:00', '2017-08-16 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 17, 407, '2017-08-17 09:30:00', '2017-08-17 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 18, 407, '2017-08-18 09:30:00', '2017-08-18 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES (18, 601, '2017-08-18 08:12:41', '2017-08-19 07:57:12');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 19, 601, '2017-08-19 07:57:12', '2017-08-19 20:14:01');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 19, 407, '2017-08-19 09:30:00', '2017-08-19 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 20, 407, '2017-08-20 09:30:00', '2017-08-20 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 21, 407, '2017-08-21 09:30:00', '2017-08-21 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 22, 601, '2017-08-22 08:07:00', '2017-08-22 20:43:10');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 22, 407, '2017-08-22 09:30:00', '2017-08-22 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 23, 407, '2017-08-23 09:30:00', '2017-08-23 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES (24, 407, '2017-08-24 09:30:00', '2017-08-24 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 24, 601, '2017-08-24 08:06:58', '2017-08-24 20:03:44');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 25, 407, '2017-08-25 09:30:00', '2017-08-25 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 26, 601, '2017-08-26 09:14:24', '2017-08-26 20:21:32');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 26, 407, '2017-08-26 09:30:00', '2017-08-26 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 27, 407, '2017-08-27 09:30:00', '2017-08-27 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 27, 601, '2017-08-27 08:48:09', '2017-08-28 07:55:24');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES (28, 407, '2017-08-28 09:30:00', '2017-08-28 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 29, 407, '2017-08-29 09:30:00', '2017-08-29 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES ( 29, 601, '2017-08-29 08:09:44', '2017-08-29 20:04:26');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES (30, 407, '2017-08-30 09:30:00', '2017-08-30 20:00:00');
INSERT INTO employee_attendance( attendance_day_id, employee_id, first_in_time, last_out_time) VALUES (31, 407, '2017-08-31 09:30:00', '2017-08-31 20:00:00');`

first table have 31 records (31 days in aug) second table have 37 records (29 records for employee_id 407 and 8 record for employee_id 601

I executed the following query

SELECT * 
FROM attendance_days
  LEFT OUTER JOIN employee_attendance 
               ON employee_attendance.attendance_day_id = attendance_days.id

It returns only 37 rows, but I expected 62 rows (31 rows each for employees)

I can't understand the issue

Improve this question
2
  • 3
    Can you explain why you expect 62 rows? Commented Sep 21, 2017 at 7:33
  • in first table 31 rows and second table , second table have records of two employees Commented Sep 21, 2017 at 7:47

1 Answer 1

Reset to default
0

postgres finds a matching row for each id of attendance_days, so there's no reason to create artificial hits (which would look like "id, attendance_date, null, null, null, null, null").

You are in fact expecting a result like "id, attendance_date, null, null, employee_id, null, null".

So you'll have to build a Cartesian product of attendance_days.id and employee_id (from the "employee table") which gives you every combination of both (62 rows). This Cartesian product can be outer joined with the employee_attendance table to yield the required result.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

But if i remove the records of employee_id 407 from the employee_attendance table it will display 31 records
Yes, 6 rows that have a matching id, and 25 rows with id's that don't match. You are expecting a result set with the complete list of attendance_days for each employee_id and, optionally the begin and end time they were in. This means each row should at least show: attendance_day.id, attendance_date and the employee_id.
Now if you look at the result of your query (without the employee 407), you'll see that in most rows the employee_id is missing. This is why you have to (left outer) join with the Cartesian product of days and employees (the list of all combinations of employees and days).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.