CODEWITHSUNDEEP
sqljoinhiveforeign-keys
Bommu
Bommu

Reputation: 247

How do i point to default dummy record incase when foreign key is null

I have query in which i join multiple dimension table to create fact table. For cases when foreign key is null i want to point it to default dummy records added in dimension tables so that there is no null value in the fields. How is it possible . Please help.

Select 
a.name,
a.id_no,
d.dealer_name,
d.address

from 
contract a
left join dealer b 
on a.con_id=b.con_id

For the above example when b.con_id is null and there is no match found in such cases i want to point to default foreign key(0) added in dealer table. Its is like if FK is null take default fk and use those record to fill the fields

Upvotes: 0

Views: 467

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can take the approach of ensuring that each dimension table has a default row, say with -1 as the value:

insert into dim_table (dim_table_id, . . . )
    values (-1, . . . );

Then in most databases, you can declare the reference as:

create table other_table (
    . . .,
    dim_table_id int not null default -1 references dim_table(dim_table_id)
    . . .
);

However, I don't think that Hive (yet) supports default values on columns. So, you can do this but you need to explicitly insert the -1 when you insert new rows into the referencing tables.

Upvotes: 1

leftjoin
leftjoin

Reputation: 38335

You can substitute with default values for not joined records like this:

Select 
a.name,
a.id_no,
case when d.con_id is null then 'Default Name' else d.dealer_name end dealer_name  ,
case when d.con_id is null then 'Default Address' else d.address  end address
from 
contract a
left join dealer d 
on a.con_id=d.con_id

Or add second join with con_id=0 function:

Select 
a.name,
a.id_no,
case when d.con_id is null then d0.dealer_name else d.dealer_name end dealer_name  ,
case when d.con_id is null then d0.address     else d.address     end address
from 
contract a
left join dealer d on a.con_id=d.con_id
left join dealer d0 on d.con_id is null and d0.con_id=0

Upvotes: 0

Gowtham Belliraj
Gowtham Belliraj

Reputation: 56

You can use ISNULL() in your query.

For example: 

select * 
from table1 as a 
left join table2 as b on a.id = ISNULL(b.id, 0)

Upvotes: 1

sudo
sudo

Reputation: 775

You can either keep it as null or you can point the fact record to a dummy record showing there is no reference for it (With an id < 0 indicating it).

Upvotes: 0

Related Questions