I have a table with users for my mail server. This table for imap authenication in dovecot:
+-------------+------------------+-------------------+------------------+------+------+---------------------------------------+--------+---------+---------------------------+
| user_name | domain_name | passwd | pwd_hash | uid | gid | mailbox_basepath | enable | quota | desc_rec |
+-------------+------------------+-------------------+------------------+------+------+---------------------------------------+--------+---------+---------------------------+
| logistic | piduna.pp.ua | loG-1990M | _no_hash_passwd_ | 2000 | 2000 | /home/maildir/piduna.pp.ua/ | 1 | 2048000 | box for logistic |
| 1c | piduna.pp.ua | 1c_user_1c | _no_hash_passwd_ | 2000 | 2000 | /home/maildir/piduna.pp.ua | 1 | 2048000 | Denisyuk V.V. |
| admin | piduna.pp.ua | AAddMmM1N | _no_hash_passwd_ | 2000 | 2000 | /home/maildir/piduna.pp.ua | 1 | 2048000 | Admin |
| al.service | piduna.pp.ua | Alumo_Serv4321 | _no_hash_passwd_ | 2000 | 2000 | /home/maildir/piduna.pp.ua | 1 | 2048000 | Alumo Service |
I need to create table with two columns. First column, all@piduna.pp.ua. Second column it is all my email accounts. In one table, I know how make concatenation from user_name and domain_name. Look:
CREATE VIEW `forwardings_all_view` AS select lcase(concat(`users`.`user_name`,'@',`users`.`domain_name`)) AS `email_fqn` from `users` where (`users`.`enable` = 1)
But, how to add in it VIEW, second column: all@piduna.pp.ua.
Like that:
+------------------------------+-------------------------------------------------+
| email_fqn | source |
+------------------------------+-------------------------------------------------+
| .logistic@piduna.pp.ua | all@piduna.pp.ua |
| 1c@piduna.pp.ua | |
| admin@piduna.pp.ua | |
| al.service@piduna.pp.ua | |
Try this:
create view first_user as
select user_name from users where enable limit 1;
create view forwardings_all_view as
select lcase(concat(a.`user_name`,'@',a.`domain_name`)) AS `email_fqn`,
if(b.user_name is null, '', 'all@piduna.pp.ua') `source`
from `users` a
left join first_user b on a.user_name = b.user_name
where a.`enable` = 1;