Reputation: 470
I need to know if its possible to retrieve non-existent data with a sql query. The Production table have three colums with 4 records but the Product2 have no Media2 record and the Product3 have no Media1 record and I would like to show these non-existent record with 0 value:
Products Media CNT
------------------------------------------------------------------------
Product1 Media1 5
Product1 Media2 7
Product2 Media1 30
Product3 Media2 12
and my desired result ( if its possible) would be:
Products Media CNT
------------------------------------------------------------------------
Product1 Media1 5
Product1 Media2 7
Product2 Media1 30
Product2 Media2 0
Product3 Media1 0
Product3 Media2 12
Upvotes: 0
Views: 44
Reputation: 3038
As it was mentiond in comments - you have to normalize your model. Technically you need to get cartesian join of unique products and media values, but for large table this will be unacceptable approach due to performance issue:
SQL> with t
2 as (
3 select 'Product1' Products,'Media1' Media, 5 CNT from dual union all
4 select 'Product1','Media2', 7 from dual union all
5 select 'Product2','Media1', 30 from dual union all
6 select 'Product3','Media2', 12 from dual
7 )
8 ,
9 cart_t as (select p.products, g.media
10 from (select unique products from t) p, (select unique media from t) g)
11 select cart_t.products, cart_t.media, nvl(CNT,0) cnt from t , cart_t
12 where cart_t.products = t.products(+)
13 and cart_t.media = t.media(+)
14 order by 1,2
15 /
PRODUCTS MEDIA CNT
-------- ------ ----------
Product1 Media1 5
Product1 Media2 7
Product2 Media1 30
Product2 Media2 0
Product3 Media1 0
Product3 Media2 12
Upvotes: 1