Re: Query from two tables return error

On Fri, Apr 1, 2016 at 11:33 AM, arnaud gaboury <arnaud(dot)gaboury(at)gmail(dot)com>
wrote:

> I have one table email.mail.mailusers :
>
> Table "email.mailusers"
> Column | Type |
> Modifiers | Storage
> | Stats target | Description
>
> -------------+--------------------------+---------------------------------------------+--------
> --+--------------+-------------
> username | text | not
> null | extende
> d | |
> password | text | not
> null | extende
> d | |
> domain_name | text | not null default '
> thetradinghall.com'::text | extende
> d | |
> created | timestamp with time zone | not null default
> now() | plain
> | |
> Indexes:
> "mailusers_pkey" PRIMARY KEY, btree (username, domain_name)
> Foreign-key constraints:
> "mailusers_domain_fk" FOREIGN KEY (domain_name) REFERENCES
> email.domainlist(domain_name)
>
> username | password | domain_name | created
>
> ----------------+--------------------------------------------------------------+---------------
> arnaud.gaboury | XXXXYYYYYY | thetradinghall.com | 2016-02-04
> 09:48:58.834774+01
>
> admin |XXXYYYYYY | thetradinghall.com | 2016-03-29
> 09:58:14.599743+02
>
> postmaster | XXXYYYYYY | thetradinghall.com | 2016-03-31
> 16:36:18.96176+02
>
> ----------------------------------------------------------
>
> I have one view email.mail_dir :
>
> View "email.mail_dir"
> Column | Type | Modifiers | Storage | Description
> ----------+------+-----------+----------+-------------
> home_dir | text | | extended |
> View definition:
> SELECT ((mailusers.domain_name || '/'::text) || mailusers.username) ||
> '/'::text AS home_dir
> FROM email.mailusers;
>
> home_dir
> ------------------------------------
> thetradinghall.com/arnaud.gaboury/
> thetradinghall.com/admin/
> thetradinghall.com/postmaster/
>
> ---------------------------------------------
>
> What I am trying to do: I want the <home_dir> be returned for <username> u.
>
> The query :
> SELECT d.home_dir
> FROM email.mail_dir d, email.mailusers u
> WHERE u.username='arnaud.gaboury';
>
> But it returns:
> home_dir
> ------------------------------------
> thetradinghall.com/arnaud.gaboury/
> thetradinghall.com/admin/
> thetradinghall.com/postmaster/
> (3 rows)
>
> It is obviously not what I expect. I am expecting one answer in this
> case: thetradinghall.com/arnaud.gaboury/
>
> Thank you for any help. I have been trying with no sucess. Maybe one
> solution could be to create a new view with <home_dir> | username as
> columns ?
>
>
>
> EDIT : I found a solution with creating a new view this way:

SELECT domain_name||'/'||username||'/' AS home_dir,username
FROM email.mailusers;

home_dir | username
------------------------------------+----------------
thetradinghall.com/arnaud.gaboury/ | arnaud.gaboury
thetradinghall.com/admin/ | admin
thetradinghall.com/postmaster/ | postmaster

Now I can pick up the home_dir for username u.