sql oracle: get null values when type does not exist

Question

I have a table phone with those values

emplid  type    phone
1       HOME    23452
2       HOME    15284
2       BUSN    25523
3       HOME    26542

I want for each emplid his HOME and BUSN phone. When he has no BUSN phone it has to be null. So my result has to be:

emplid  type    phone
1       HOME    23452
2       HOME    15284
2       BUSN    25523
3       HOME    26542
1       BUSN    null
3       BUSN    null

I tried to join with a dummy table

(select 'HOME'as typ from dual
                union select 'HOM2' from dual )

but it does not give me the desired result. I don't know how I can join it with my phone table

Answer 1

One option would be using the logic with not in

with phone(emplid, type, phone) as 
(
 select 1, 'HOME', 23452 from dual union all
 select 2, 'HOME', 15284 from dual union all
 select 2, 'BUSN', 25523 from dual union all
 select 3, 'HOME', 26542 from dual
)
select * from phone
union all
select emplid, 'BUSN', null 
  from phone
 where emplid not in 
       ( select emplid 
           from phone p 
          where type = 'BUSN'
            and p.emplid = emplid );

EMPLID  TYPE    PHONE
------  ----    -----
1       HOME    23452
2       HOME    15284
2       BUSN    25523
3       HOME    26542
3       BUSN    NULL
1       BUSN    NULL

Demo

Answer 2

Try this

with 
phone as (
    select 1 as emplid, 'HOME' as type, '23452' as phone from dual union
    select 2, 'HOME', '15284' from dual union
    select 2,'BUSN','25523' from dual union
    select 3,'HOME','26542' from dual),
types as (
    select distinct type from phone 
    )
select phone.emplid, types.type, phone.phone 
 from types
  left join phone partition by (emplid)
      on phone.type = types.type 

Answer 3

Many versions ago, Oracle introduced partitioned outer join just for this kind of problem. https://docs.oracle.com/cd/E11882_01/server.112/e25555/tdpdw_sql.htm#TDPDW0072

You do need a table with the various types that need to be included. Or, as you were trying to do, you can create it on the fly. IN ADDITION, you need the "partition" clause to the outer join. It is shown in ALL CAPS in the code below (so that it can be found easily).

As a separate and unrelated thing, in the "helper" subquery that I create to substitute for an actual "types" table I also create an ORD column, to use for ordering. This is only needed if you want to always show the HOME number before the BUSN number in the main query. Of course, there are other ways to achieve the same outcome, but since we are creating a helper subquery anyway, we get this at essentially no additional cost.

with
  phone(emplid, type, phone) as (
    select 1, 'HOME', 23452 from dual union all
    select 2, 'HOME', 15284 from dual union all
    select 2, 'BUSN', 25523 from dual union all
    select 3, 'HOME', 26542 from dual
  )
-- end of sample data (for testing only, not part of the actual query)
select p.emplid, h.type, p.phone
from   (
         select 'HOME' as type, 1 as ord from dual union all
         select 'BUSN'        , 2        from dual
       ) h
       left outer join phone p  PARTITION BY (EMPLID)
       on h.type = p.type
order by p.emplid, h.ord     
;

    EMPLID TYPE      PHONE
---------- ---- ----------
         1 HOME      23452
         1 BUSN           
         2 HOME      15284
         2 BUSN      25523
         3 HOME      26542
         3 BUSN          

Answer 4

You need to get all possible emplid values and cross-join with all possible type values, and then see which ones actually exist using an outer join.

For example, if you want to base this entirely on other data in your phone table you could get the distinct values for the first two columns using inline views:

-- CTE for your sample data
with phone (emplid, type, phone) as (
  select 1, 'HOME', 23452 from dual
  union all select 2, 'HOME', 15284 from dual
  union all select 2, 'BUSN', 25523 from dual
  union all select 3, 'HOME', 26542 from dual
)
-- actual query
select e.emplid, t.type, p.phone
from (select distinct emplid from phone) e
cross join (select distinct type from phone) t
left join phone p on p.emplid = e.emplid and p.type = t.type;

    EMPLID TYPE      PHONE
---------- ---- ----------
         1 HOME      23452
         2 HOME      15284
         2 BUSN      25523
         3 HOME      26542
         3 BUSN           
         1 BUSN           

But you might really want to get the possible emplid values from, say, an employee table - in which case you'd see null values for all employees even if they had no phone records at all; and you might want to get the possible type values from some other table, or hard-code the list:

select e.emplid, t.type, p.phone
from (select distinct emplid from phone) e -- or more likely from a separate employee table
cross join (select 'HOME' as type from dual union all select 'HOM2' from dual) t
left join phone p on p.emplid = e.emplid and p.type = t.type;

    EMPLID TYPE      PHONE
---------- ---- ----------
         1 HOME      23452
         2 HOME      15284
         3 HOME      26542
         1 HOM2           
         2 HOM2           
         3 HOM2           

I've stuck with the 'HOM2' value you used, even though that wasn't in your sample data at all; but as you can see you get null entries for that name for all employee IDs too.

Answer 5

What you tried was the correct method though. Just another table

SELECT PhoneTypes.EmplID, PhoneTypes.Type, Phone
FROM Phone
RIGHT OUTER JOIN (SELECT Distinct emplid, T.Type FROM Phone, (select 'HOME' FROM DUAL as type union select 'HOM2' FROM DUAL union select 'BUSN' FROM DUAL) T) PhoneTypes
   ON Phone.type=PhoneTypes.Type

Answer 6

Try like this. I think it is similar to your approach

select t.* from Phone t
left outer join 
(
  select 'HOME'as typ from dual
  union select 'HOM2' from dual ) t2
on typ = type