How can I find the unique phone numbers (and collapse them into a single column) from table_1 (while keeping the IDs and date fields), and remove phone numbers that appear in table_2?
table_1
| ID | phone1 | phone2 | date |
|---|---|---|---|
| 1 | 1111111111 | 2021-12-31 | |
| 5 | 2222222222 | 3333333333 | 2020-11-08 |
| 8 | 5555555555 | 2021-03-15 | |
| 14 | 7777777777 | 8888888888 | 2016-10-20 |
table_2
| ID | phone1 | phone2 | date |
|---|---|---|---|
| 567 | 4444444444 | 1111111111 | 2020-11-28 |
| 660 | 8888888888 | 2018-01-01 | |
| 898 | 9999999999 | 2017-04-06 |
regardless of which phone column the phone appears in, I want to remove it from the end results. so ID 1 with phone of 1111111111 would be removed because it is in phone2 on Table 2
Desired output
| ID | phone num | date |
|---|---|---|
| 5 | 2222222222 | 2020-11-08 |
| 5 | 3333333333 | 2020-11-08 |
| 8 | 5555555555 | 2021-03-15 |
| 14 | 7777777777 | 2016-10-20 |
What I have so far, which seems to work, is this. I feel like there has to be a more efficient way to do this though.
select * from (
select id, phone1 as phone_num, date from table_1
union all
select id, phone2 as phone_num, date from table_1
) tmp
where phone_num not in (
select phone1 as phone_num from table_2
union all
select phone2 as phone_num from table_2
)
order by id desc;
