CODEWITHSUNDEEP
sqlpostgresql
Potato Science
Potato Science

Reputation: 602

Missing row using left join and generate_series

I don't understand why my two table behaves incorrectly, the generated series doesn't show on the joined table.

TABLE 1: attendance

id | employee_id | flag | entry_date
-------------------------------
1 | 1 | 0 | 2017-11-17
2 | 2 | 0 | 2017-11-17
3 | 3 | 0 | 2017-11-17

Here's my query:

SELECT
    attendance.employee_id,
    TO_CHAR(series::date, 'YYYY-MM-DD')
FROM  generate_series('2017-11-16', '2017-11-30', '1 day'::INTERVAL) series
LEFT JOIN (
    SELECT
      employee_id,
      to_char(entry_date, 'YYYY-MM-DD') entry_date
    FROM
        attendance
    WHERE entry_at >= '2017-11-16' AND entry_at <= '2017-11-30'
    GROUP BY to_char(entry_at, 'YYYY-MM-DD'), employee_id
) attendance ON attendance.entry_date = TO_CHAR(series::date, 'YYYY-MM-DD')
ORDER by employee_id

The result is:

employee_id | to_char 
------------------------
1 | 2017-11-17
2 | 2017-11-17
3 | 2017-11-17

I'm expecting a little bit different in which 2017-11-16 will show since my generated series started at said date.

Expected result:

employee_id | to_char 
------------------------
null | 2017-11-16 
null | 2017-11-16
null | 2017-11-16
1 | 2017-11-17
2 | 2017-11-17
3 | 2017-11-17

Here's a sample SQL Fiddle to test: http://sqlfiddle.com/#!17/d7907/3

Update [Jan/4/2017]: Looks like i'm thinking on the wrong side, I decided to perform a correlated subquery

What I wanted to do is to count the number of dates present according to the generated_series.

Upvotes: 0

Views: 634

Answers (1)

Tomasz Myrta
Tomasz Myrta

Reputation: 1144

It's hard to understand, what you really need. What you described as "The result is" - these are rows from your internal query. If you left join generate_series with this internal query - you will get 1 null row for every date excluding 2017-11-17 and 3 rows for 2017-11-17. I checked it - it worked as expected:

 employee_id |  to_char
-------------+------------
           1 | 2017-11-17
           2 | 2017-11-17
           3 | 2017-11-17
             | 2017-11-20
             | 2017-11-21
             | 2017-11-22
             | 2017-11-23
             | 2017-11-24
             | 2017-11-25
             | 2017-11-26
             | 2017-11-27
             | 2017-11-28
             | 2017-11-29
             | 2017-11-16
             | 2017-11-30
             | 2017-11-18
             | 2017-11-19

Upvotes: 1

Related Questions