Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Gary.

Asked: August 04, 2022 - 5:34 pm UTC

Last updated: August 05, 2022 - 12:13 am UTC

Version: 19c

Viewed 100+ times

You Asked

hello I'm curious how you would deal with the following.

Table t1 has mixture of usernames, some with format of email address, some with format of first_name.last_name.
Table t2 lookup table has username in email format.
Requirement to return all 3 rows

create table t1 (taskid number, username varchar2(50))

insert into t1 values (1,'john.smith@12]3.com');
insert into t1 values (2,'dave.jones@123.com');
insert into t1 values (3,'dave.smith');

commit;

create table t2 (username varchar2(50));

insert into t2 values ('john.smith@123.com');
insert into t2 values ('dave.smith@123.com');

commit;


Via ansi syntax, quite forward:

select *
from t1
left outer join t2
on t1.username = t2.username
or 
t1.username  = substr(t2.username,1,instr(t2.username,'@')-1)
order by 1


Via oracle syntax - As can't use outer joins with OR, do you have any (easier) alternatives than this?

select t1.taskid, t1.username, t2.username
from t1, t2
where t1.username = t2.username
union all 
select t1.taskid, t1.username, t2.username
from t1, t2
where t1.username = substr(t2.username,1,instr(t2.username,'@')-1)
union all 
select t1.taskid, t1.username, to_char(null)
from t1
where not exists 
    (select 1 
    from t2
    where t1.username = t2.username 
    or t1.username = substr(t2.username,1,instr(t2.username,'@')-1))
order by 1



thanks


and Connor said...

A lateral does the trick nicely

SQL> select t1.*, x.username
  2  from t1,
  3       lateral(
  4         select t2.username
  5         from   t2
  6         where  t1.username = t2.username
  7         or t1.username = substr(t2.username,1,instr(t2.username,'@')-1)
  8         ) (+) x;

    TASKID USERNAME             USERNAME
---------- -------------------- --------------------
         1 john.smith@12]3.com
         2 dave.jones@123.com
         3 dave.smith           dave.smith@123.com


Rating

  (1 rating)

Comments

A reader, August 05, 2022 - 6:12 am UTC





More to Explore

SQL

The Oracle documentation contains a complete SQL reference.