SQL: Left Outer Join, different GROUP BY, need to replicate records ?

Question

I have information about accounts in two tables (A, B). The records in A are all unique at the account level (account_id), but in table B, accounts are identified by account_id and month_start_dt, so each account may exist in zero or more months.

The trouble is, when I left outer join A to B so the joined table contains all records from A with the records from B (by account, by month) any account that does not exist in table B for a given month does not have a record for that month.

Desired outcome: If an account does not exist in table B for a given month, create a record for that account in the joined table with month_start_dt and 0 for all variables being selected from B.

As it stands, I can get the join to work where all accounts not appearing in B (not appearing at all, in any month) have 0 values for all variables being selected from B (using nvl(variable, 0) ) but, these accounts only have a single record. They should have one for each month.

I'm not sure whether I can or not. I'm at work and I don't know what legal considers proprietary. I'll try to work up an example that doesn't reference any field names or table names and what not. — justin cress, Oct 31 '11 at 20:08

score 1 · Accepted Answer · answered Oct 31 '11 at 20:11

1

Create a temp table with number of records you want for not-existing rows and right join the result of first query.

select tbl.* from ( select * from A left join B on a.col1 = b.col2) tbl join tmpTable on tbl.col2 = tmpTable.zerocol

try this.

answered Oct 31 '11 at 20:11

Nps

1,638
4
20
40

You're right. What I just realized is cross joining a list of accounts on a list of months gives a table which can be left joined on table B without any problems. – justin cress Oct 31 '11 at 20:19

onedaywhen · Answer 2 · 2011-11-01T11:07:25.503

I don't see why you need an outer join. This uses Standard SQL's EXCEPT (MINUS in Oracle):

SELECT account_id, month_start_dt, all_variables 
  FROM B
UNION
(
 SELECT account_id, month_start_dt, 0 AS all_variables
   FROM A 
        CROSS JOIN (
                    SELECT DISTINCT month_start_dt
                      FROM B
                   ) AS DT1
 EXCEPT 
 SELECT account_id, month_start_dt, 0 AS all_variables
   FROM B
);

score 1 · Answer 3 · edited May 23 '17 at 11:47

You could use a tally Calendar table, with months (of several years). See this similar question: How to create a Calender table for 100 years in Sql

And then have:

FROM 
        A
    CROSS JOIN
        ( SELECT y
               , m
          FROM Calendar
          WHERE (   y = @start_year
                AND m >= @start_month
                )
             OR (   y > @start_year
                AND y < @end_year
                )
             OR (   y = @end_year
                AND m <= @end_month
                )
        ) AS C
    LEFT JOIN
        B
            ON  B.account_id = A.account_id
            AND YEAR(B.start_date) = C.y
            AND MONTH(B.start_date) = C.m

SQL: Left Outer Join, different GROUP BY, need to replicate records ?

3 Answers3