Creating one new column out of two existing columns-CodePudding

I have this table t1 with c1:old_email and c2:new_email

The goal: I want to create a new column/or query this table in a way so that I can use fields from c1 and c2 (basically merge the results from c1 and c2 into one column c3) and use it for a subquery in a where statement:

Select * from t2 where t2.email=(select c3 from t1)

name    |old_email  |new_email

:Johnny Go: [email protected]:   
:Bertie Post:   [email protected]: [email protected]:

CodePudding user response：

can't you join using both conditions?

select t2.* from
t2 join t1 on t2.email in (t1.old_email, t1.new_email)

select t2.*
from t1, t2
where t2.email = t1.old_email 
or t2.email = t1.new_email

CodePudding user response：

The question is a little ambiguous. I am going to infer you have a table like the one below, and you want to match either email address by joining a separate table of email addresses you have to identify matches.

ID	NAME	OLD_EMAIL	NEW_EMAIL
1	David Lin	[email protected]	[email protected]
2	Christy Thomas	[email protected]	[email protected]
3	Erin Hill	[email protected]	[email protected]
4	Noah Collins	[email protected]	[email protected]
5	Andrew Salazar	[email protected]	[email protected]

You are going to want to put both old_email and new_email in one column. We can do this with unpivot.

select 
    p.*
from t1
   unpivot(email for email_field in (old_email, new_email)) p;

The result would look like so.

ID	NAME	EMAIL_FIELD	EMAIL
1	David Lin	OLD_EMAIL	[email protected]
1	David Lin	NEW_EMAIL	[email protected]
2	Christy Thomas	OLD_EMAIL	[email protected]
2	Christy Thomas	NEW_EMAIL	[email protected]
3	Erin Hill	OLD_EMAIL	[email protected]
3	Erin Hill	NEW_EMAIL	[email protected]
4	Noah Collins	OLD_EMAIL	[email protected]
4	Noah Collins	NEW_EMAIL	[email protected]
5	Andrew Salazar	OLD_EMAIL	[email protected]
5	Andrew Salazar	NEW_EMAIL	[email protected]

Now you can join your secondary table of emails to perform email matching with a query like something below.

with t1_cte as (
select 
     p.*
 from t1
    unpivot(email for email_field in (old_email, new_email)) p order by 1
)
select t1.* from t1_cte t1
inner join seperate_table_of_emails t2 -- << your secondary table
    on t1.email = t2.email;