CODEWITHSUNDEEP
sqloracle-database
Edu
Edu

Reputation: 470

Obtaining the desired result to retrieve non-existent data with a sql query

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

Answers (1)

Dmitry Nikiforov
Dmitry Nikiforov

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

Related Questions