Missing data from one table when LEFT JOIN

Question

I want to join tables below, since there are empty days in some tables, I generated dates to have all the dates and used DISTINCT ON to eliminate duplicate rows from the result.

I joined them on dates and user. (Normally there are other users, I just added one.)

Please note that each table was created in different CTEs. I just included dates generated and the join part of the query.

The only problem in my query, it doesn't give me all data from 'leads' table. How can I get all the data from each table?

    WITH dates AS (SELECT d::DATE AS DATE FROM GENERATE_SERIES('2018-01-01', CURRENT_DATE, '1 day'::INTERVAL) d )

        SELECT DISTINCT ON (dates.date) dates.date,
        calls.user,
        leads.leads_total,
        calls.calls_total,
        deals.deals_total,
        cases.cases_total
    FROM dates
    LEFT JOIN calls ON
    calls.call_date = dates.date
    LEFT JOIN leads ON
    leads.leads_date = dates.date
    AND calls.user = leads.user
    LEFT JOIN cases ON
    cases.case_date= dates.date
    AND calls.user = cases.user  
    LEFT JOIN deals ON
    deals.deals_date = dates.date
    AND calls.user = deals.user
    WHERE
        dates.date >= '2019-11-01'
        AND dates.date <= '2019-11-10'
    GROUP BY
        dates.date calls.owner_name,
        leads.leads_total,
        calls.calls_total,
        deals.deals_total,
        cases.cases_total
    ORDER BY
        dates.date DESC

Leads table:

+------------+-----------+-------------+
| leads_date |   user    | leads_total |
+------------+-----------+-------------+
| 11/9/2019  | James Joe |           4 |
| 11/8/2019  | James Joe |          10 |
| 11/7/2019  | James Joe |           7 |
| 11/6/2019  | James Joe |          12 |
| 11/5/2019  | James Joe |           8 |
| 11/4/2019  | James Joe |           8 |
| 11/2/2019  | James Joe |           2 |
| 11/1/2019  | James Joe |           4 |
+------------+-----------+-------------+

Calls table:

+-----------+-----------+-------------+
| call_date |   user    | calls_total |
+-----------+-----------+-------------+
| 11/8/2019 | James Joe |          47 |
| 11/7/2019 | James Joe |          55 |
| 11/6/2019 | James Joe |          52 |
| 11/5/2019 | James Joe |          54 |
| 11/4/2019 | James Joe |          48 |
| 11/1/2019 | James Joe |          51 |
+-----------+-----------+-------------+

Deals table:

+------------+-----------+-------------+
| deals_date |   user    | deals_total |
+------------+-----------+-------------+
| 11/8/2019  | James Joe |           1 |
| 11/7/2019  | James Joe |           1 |
| 11/6/2019  | James Joe |           3 |
| 11/4/2019  | James Joe |          16 |
| 11/1/2019  | James Joe |           1 |
+------------+-----------+-------------+

Cases table:

+-----------+-----------+-------------+
| case_date |   user    | cases_total |
+-----------+-----------+-------------+
| 11/8/2019 | James Joe |           5 |
| 11/7/2019 | James Joe |           4 |
| 11/6/2019 | James Joe |          12 |
| 11/4/2019 | James Joe |           5 |
| 11/1/2019 | James Joe |           2 |
+-----------+-----------+-------------+

Desired output:

Pilosa Pilosa · Accepted Answer · 2019-12-10T17:27:33

You are joining leads on calls.user:

LEFT JOIN leads ON
    leads.leads_date = dates.date
    AND calls.user = leads.user

Since calls contains 6 rows only some lead data are discarded on this join.

Suggested solution

Extract all users and CROSS JOIN with the dates. Then you will get one line for each date/user combination.

WITH
    dates AS (
        SELECT
            d::DATE AS date
        FROM
            GENERATE_SERIES('2018-01-01', CURRENT_DATE, '1 day'::INTERVAL) d
    ),
    users AS (
        SELECT "user" FROM leads
        UNION SELECT "user" FROM calls
        UNION SELECT "user" FROM deals
        UNION SELECT "user" FROM cases
    )
    SELECT 
        dates.date,
        users.user,
        leads.leads_total,
        calls.calls_total,
        deals.deals_total,
        cases.cases_total
    FROM
        dates
        CROSS JOIN users
        LEFT OUTER JOIN leads ON leads.leads_date = dates.date AND leads.user = users.user
        LEFT OUTER JOIN calls ON calls.call_date = dates.date AND calls.user = users.user
        LEFT OUTER JOIN deals ON deals.deals_date = dates.date AND deals.user = users.user
        LEFT OUTER JOIN cases ON cases.case_date = dates.date AND cases.user = users.user
    WHERE
        dates.date >= '2019-11-01'
        AND dates.date <= '2019-11-10'
    ORDER BY
        users.user,
        dates.date DESC

Missing data from one table when LEFT JOIN

1 Answers