I have a first table called emails with a list of all the emails of my colleagues
| email | | ----------------------- | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="eb988a998a868a828aab8e868a8287c5888486">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="107d797765757c767562627579627150757d71797c3e737f7d">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="97fdf8f6f8e4fefbe1f6d7f2faf6fefbb9f4f8fa">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="1c76737d727d717d757d5c79717d7570327f7371">[email protected]</a> |
I have a second table called aliases, with a list of all the secondary emails/aliases my colleagues are using
| alias1 | alias2 | | ------------------------ | ------------------- | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="c8a2a7a9a6a9a5a9a1a988ada5a9a1a4e6aba7a5">[email protected]</a> | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="80ede1e9e1eaefe1eee1c0e5ede1e9ecaee3efed">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d1bcb0b8b0bbbeb0bfb091b4bcb0b8bdffb2bebc">[email protected]</a> | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="8ae7ebe3ebcaefe7ebe3e6a4e9e5e7">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="aac7c3cddfcfc6cccfd8d8cfc3d8cbeacfc7cbc3c684c9c5c7">[email protected]</a> | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="3c51555b4959507c59515d5550125f5351">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="83eee2eae2c3e6eee2eaefade0ecee">[email protected]</a> | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="b1dbded0dfd0f1d4dcd0d8dd9fd2dedc">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="204a4f414e414d41494160454d41494c0e434f4d">[email protected]</a> | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="02686d6f636b6342676f636b6e2c616d6f">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="640e0b050a05240109050d084a070b09">[email protected]</a> | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="12787f737b7352777f737b7e3c717d7f">[email protected]</a> |
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 | | ------------------- | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="c7ada8a6a9a6aaa6aea687a2aaa6aeabe9a4a8aa">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="cba1a4a6aaa2aa8baea6aaa2a7e5a8a4a6">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="19747870787376787778597c74787075377a7674">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="ee838f878fae8b838f8782c08d8183">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e98386888788a98c84888085c78a8684">[email protected]</a> | | <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d7bdbab6beb697b2bab6bebbf9b4b8ba">[email protected]</a> |
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?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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 '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="a5cfcac4cbc4c8c4ccc4e5c0c8c4ccc98bc6cac8">[email protected]</a>' 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 ------------------- <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="600a0f010e010d01090120050d01090c4e030f0d">[email protected]</a> <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="99f4f8f0f8f3f6f8f7f8d9fcf4f8f0f5b7faf6f4">[email protected]</a> <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="90fafffdf1f9f1d0f5fdf1f9fcbef3fffd">[email protected]</a> <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="701d11191130151d11191c5e131f1d">[email protected]</a> <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="305a5f515e5170555d51595c1e535f5d">[email protected]</a> <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="adc7c0ccc4ccedc8c0ccc4c183cec2c0">[email protected]</a>
See running example at DB Fiddle.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0