CODEWITHSUNDEEP
sqlsql-server
S.Jose
S.Jose

Reputation: 246

Join tables to get missing records

I have a reference table

NAV_ID int
NAV_FRQ varchar(20)

values 

1 DAILY     
2 WEEKLY 
3 MONTHLY

And a datatable

CUST_ID varchar(10)
NAV_FRQ varchar(20)
NAV_VALUE INT

VALUES

C1  DAILY   10
C2  DAILY   20
C3  WEEKLY  40
c3  MONTHLY 80

I'm trying to get following output

C1  DAILY       10
C1  WEEKLY      NULL
C1  MONTHLY     NULL
C2  DAILY       20
C2  WEEKLY      Null
C2  MONTHLY     NULL
C3  DAILY       NULL
C3  WEEKLY      40
C3  MONTHLY     80

I tried following query, but this is not working, 

select a.CUST_ID
          ,b.NAV_FRQ
         ,CASE WHEN a.NAV_FRQ = b.NAV_FRQ THEN b.NAV_VALUE ELSE NULL END AS NAV_VALUE
    from table_a a cross join table_b b

Upvotes: 0

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use a cross join to generate the rows and a left join to bring in the existing values:

select c.cust_id, n.nav_frq, d.nav_value
from nav n cross join
     (select distinct cust_id from data) c left join
     data d
     on d.nav_id = n.nav_id and d.cust_id = c.cust_id
order by c.cust_id, n.nav_id;

Upvotes: 1

Thom A
Thom A

Reputation: 95561

You need to CROSS JOIN to your customer table (which I assume you have, but haven't provided us an example of) and then LEFT JOIN. Thus:

SELECT C.CUST_ID,
       RT.NAV_FRQ,
       DT.NAV_VALUE
FROM Customer C
     CROSS JOIN ReferenceTable RT
     LEFT JOIN DataTable DT ON C.CUST_ID = DT.Cust_ID
                           AND RT.NAV_FRQ = DT.NAV_FRQ;

If you don't have a customer table, then I would suggest making one as your table DataTable won't have a unique value without combining multiple columns (and that asks the question, where is the customer's information stored?) and leaves your data structure in an un-normalised state.

Upvotes: 1

Related Questions