query regarding joining of two tables

Question

Suppose I have 2 below tables

sql> select * from fraud_types ;

fraud_id       fraud_name
--------       ----------
       1       Fraud 1
       2       Fraud 2
       3       Fraud 3
       4       Fraud 4
       5       Fraud 5


sql> select * from alarms ;

fraud_id       dealer      count
--------       ------      -----
       1       Deal 1          5
       3       Deal 1          3
       5       Deal 1          4
       1       Deal 2          2
       2       Deal 2          6
       3       Deal 2          1
       4       Deal 2          7
       5       Deal 2          9

I want to join the two tables and get the output as

dealer      fraud_id        count
------      --------        -----
Deal 1             1            5
Deal 1             2            0
Deal 1             3            3
Deal 1             4            0
Deal 1             5            4
Deal 2             1            2
Deal 2             2            6
Deal 2             3            1
Deal 2             4            7
Deal 2             5            9

Basically I want to include the fields from fraud_types also and just display 0 in the output if it is not present in the alarms table. How can I achieve this ? Please help

Regards

Answer 1

Partitioned outer join is very useful for cases like this:

select a.dealer, f.fraud_id, nvl(a.count,0) count
  from fraud_types f
  left outer join alarms a
      partition by (a.dealer)
      on a.fraud_id = f.fraud_id
 order by a.dealer, f.fraud_id

This does an outer join between alarms and fraud_types for every value of dealer found in alarms.

--

If the alarms table does not have (fraud,dealer) as key, then you can do a group by before the partition outer join:

select a.dealer, f.fraud_id, nvl(a.count,0) count
  from fraud_types f
  left outer join (
     select fraud_id
          , dealer
          , sum(count) count
       from alarms
      group by fraud_id, dealer
  ) a
      partition by (a.dealer)
      on a.fraud_id = f.fraud_id
 order by a.dealer, f.fraud_id

Answer 2

   select distinct f.fraud_id,dealer,
   (case when f.fraud_id=t.fraud_id then COUNT else 0 end) counts

   from 
   fraud_types f
   left  join
   alarms t
   partition by (dealer)
   on f.fraud_id=t.fraud_id 
   order by dealer

Answer 3

You can do this with a cross join to get all combinations and then a left outer join:

select d.dealer, f.fraud_id, coalesce(cnt, 0)
from (select distinct dealer from fraud_types) d cross join
     fraud_types f left outer join
     (select dealer, fraud_id, count(*) as cnt
      from fraud_types
      group by dealer, fraud_id
     ) df
     on df.dealer = d.dealer and df.fraud_id = f.fraud_id
order by d.dealer, f.fraud_id;