0

I have e-mail aliases tables that looks like this:

+---------------------------------+--------------------------+-------------------------+-------------+---------------------+---------------------+---------------------+--------+
| address                         | name                     | accesspolicy            | domain      | created             | modified            | expired             | active |
+---------------------------------+--------------------------+-------------------------+-------------+---------------------+---------------------+---------------------+--------+
| user1alias1@domain.com          | User1 Frist_LastName     | User1_email@domain.com  | domain.com  | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |
| user1alias2@domain.com          | User1 Frist_LastName     | User1_email@domain.com  | domain.com  | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |
| user1alias3@domain.com          | User1 Frist_LastName     | User1_email@domain.com  | domain.com  | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |
| user1alias4@domain.com          | User1 Frist_LastName     | User1_email@domain.com  | domain.com  | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |

| user2alias1@domain.com          | User2 Frist_LastName     | User2_email@domain.com  | domain.com  | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |
| user2alias2@domain.com          | User2 Frist_LastName     | User2_email@domain.com  | domain.com  | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |
| user2alias3@domain.com          | User2 Frist_LastName     | User2_email@domain.com  | domain.com  | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |

| user1alias1@domain2.com         | User1 Frist_LastName     | User1_email@domain2.com | domain2.com | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |
| user1alias2@domain2.com         | User1 Frist_LastName     | User1_email@domain2.com | domain2.com | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |
| user1alias3@domain2.com         | User1 Frist_LastName     | User1_email@domain2.com | domain2.com | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |

| user2alias1@domain2.com         | User2 Frist_LastName     | User2_email@domain2.com | domain2.com | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |
| user2alias2@domain2.com         | User2 Frist_LastName     | User2_email@domain2.com | domain2.com | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |
| user2alias3@domain2.com         | User2 Frist_LastName     | User2_email@domain2.com | domain2.com | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |
| user2alias4@domain2.com         | User2 Frist_LastName     | User2_email@domain2.com | domain2.com | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | 9999-12-31 00:00:00 |      1 |
+---------------------------------+--------------------------+-------------------------+-------------+---------------------+---------------------+---------------------+--------+

I'm looking how can create (select / create view) result outcome that looks something like this:

+---------------------------------+--------------------------+--------------+-------------------------+-------------------------+-------------------------+-------------------------+--------+
| accesspolicy                    | name                     | domain       | alias1                  | alias2                  | alias3                  | alias4                  | active |
+---------------------------------+--------------------------+--------------+-------------------------+-------------------------+-------------------------+-------------------------+--------+
| User1_email@domain.com          | User1 Frist_LastName     | domain.com   | user1alias1@domain.com  | user1alias2@domain.com  | user1alias3@domain.com  | user1alias4@domain.com  |      1 |
| User1_email@domain2.com         | User1 Frist_LastName     | domain2.com  | user1alias1@domain2.com | user1alias2@domain2.com | user1alias3@domain2.com | NULL                    |      1 |
| User2_email@domain.com          | User2 Frist_LastName     | domain.com   | user2alias1@domain.com  | user2alias2@domain.com  | user2alias3@domain.com  | NULL                    |      1 |
| User2_email@domain2.com         | User2 Frist_LastName     | domain2.com  | user2alias1@domain2.com | user2alias2@domain2.com | user2alias3@domain2.com | user2alias4@domain2.com |      1 |
+---------------------------------+--------------------------+--------------+-------------+---------------------+---------------------+---------------------+--------+
  • It's MySQL 5.7.24 server. and I need to create a view table for e-mail server (sogo). In all the cases active=1 if not record is deleted but for table view the column is not required. In the view tables required columns are (accesspolicy, name, domain, alias 1-2-3-4-5-6-7-*) – Saulius Nov 5 '18 at 8:49
  • As I understand the maximum count of aliases for single accesspolicy is not limited. Is it? – Akina Nov 5 '18 at 8:57
  • no it's not, but lets assume that I limit to 10. – Saulius Nov 5 '18 at 9:55
0

Think about the next solution. You use the query:

SELECT accesspolicy, name, domain, GROUP_CONCAT(DISTINCT address) all_aliases
FROM source_table
GROUP BY accesspolicy, name, domain

Additionally you may add ORDER BY clause into GROUP_CONCAT if needed.

Its output looks like:

+--------------------------+-----------------------+--------------+-------------------------------------------------------------------------------------------------+
| accesspolicy             | name                  | domain       | all_aliases                                                                                     |
+--------------------------+-----------------------+--------------+-------------------------------------------------------------------------------------------------+
| User1_email@domain.com   | User1 Frist_LastName  | domain.com   | user1alias1@domain.com,user1alias2@domain.com,user1alias3@domain.com,user1alias4@domain.com     |
| User1_email@domain2.com  | User1 Frist_LastName  | domain2.com  | user1alias1@domain2.com,user1alias2@domain2.com,user1alias3@domain2.com                         |
| User2_email@domain.com   | User2 Frist_LastName  | domain.com   | user2alias1@domain.com,user2alias2@domain.com,user2alias3@domain.com                            |
| User2_email@domain2.com  | User2 Frist_LastName  | domain2.com  | user2alias1@domain2.com,user2alias2@domain2.com,user2alias3@domain2.com,user2alias4@domain2.com |
+--------------------------+-----------------------+--------------+-------------------------------------------------------------------------------------------------+

Then, on the client side, you may (if needed) split the all_aliases column to separate columns. For to know the number of columns which you need to create in output grid you may previously ask it with the query:

SELECT MAX(cnt) columns_count
FROM ( SELECT COUNT(DISTINCT address)
       FROM source_table
       GROUP BY accesspolicy, name, domain ) x

If a combination (accesspolicy, name, domain, address) is unique (checked by unique index) you may remove DISTINCTs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.