CODEWITHSUNDEEP
mysql
Tiago M
Tiago M

Reputation: 19

MySQL - Recursive - getting email addresses from 2 different tables and columns

I have a first table called emails with a list of all the emails of my colleagues

| email                    |
| -----------------------  |
| [email protected]       |
| [email protected] |
| [email protected]      |
| [email protected]      |

I have a second table called aliases, with a list of all the secondary emails/aliases my colleagues are using

| alias1                   | alias2              |
| ------------------------ | ------------------- |
| [email protected]      | [email protected] |
| [email protected]      | [email protected]      |
| [email protected] | [email protected]    |
| [email protected]           | [email protected]     |
| [email protected]      | [email protected]    |
| [email protected]          | [email protected]     |

I can see that the users [email protected] and [email protected] are using aliases. But let's focus on the user [email protected].

I need to get a list of all the email addresses the user [email protected] is using. The difficult part is that I need to get a list with the main email address plus all the intersections where the first email and consecutive ones are being used by this user. The end result should look like this

| emails              |
| ------------------- |
| [email protected] |
| [email protected]    |
| [email protected] |
| [email protected]      |
| [email protected]     |
| [email protected]     |

If I do WHERE email='[email protected]' it should look like this, but I also need the same result if I do WHERE email='[email protected]'

I've been through some days of testing queries and I don't seem to have a solution for this (I've been using right joins, full outer joins and unions, but no luck so far). Is there a good way to do this?

Upvotes: 0

Views: 49

Answers (1)

The Impaler
The Impaler

Reputation: 48770

You can use a recursive CTE to walk the graph and get the full list of interconnected aliases. Care needs to be taken to handle cycles; that requires the query to use UNION instead of the traditional UNION ALL to separate the anchor and recursive member of the CTE.

The query can take the form:

with recursive
n as (
  select '[email protected]' as email
 union
  select case when a.alias1 = n.email then a.alias2 else a.alias1 end
  from n
  join aliases a on (a.alias1 = n.email or a.alias2 = n.email)
    and a.alias1 <> a.alias2
)
select * from n;

Result:

 email               
 ------------------- 
 [email protected] 
 [email protected] 
 [email protected]    
 [email protected]      
 [email protected]     
 [email protected]

See running example at DB Fiddle.

Upvotes: 1

Related Questions