Re: Query from two tables return error

On 1 April 2016 at 11:33, 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.
>

One of many difficulties with computers that they do what you say them to
do, not what you think or you think you are saying. :)
Lets see:

SELECT d.home_dir
FROM email.mail_dir d, email.mailusers u <- make a join between mail_dir
and mailusers = join every(!) record from the first table with every(!)
record from the second table
WHERE u.username='arnaud.gaboury'; <- but I need only those from the joined
records where the username is arnaud.gaboury

And there, you have it.

> But it returns:
> home_dir
> ------------------------------------
> thetradinghall.com/arnaud.gaboury/
> thetradinghall.com/admin/
> thetradinghall.com/postmaster/
> (3 rows)
>

You would have seen the problem, if you had used * instead d.homedir.
And there is no solution to your problem in the given circumstances.
You need the usename field in the view as well and then you can:

SELECT d.home_dir
FROM email.mail_dir d, email.mailusers u
WHERE u.username='arnaud.gaboury'
and u.usename=d.usename;

But it's completely unnecessary.

You can simply redefine the view.

SELECT *,((mailusers.domain_name || '/'::text) || mailusers.username) ||
'/'::text AS home_dir
FROM email.mailusers;

Notice the * after the SELECT statement.
So you have all the data plus the homedir.

You can leave out the whole view thing and incorporate the home_dir
expression right into your select.
Or you can write a function which makes this to you with the usename as
argument.

Regards,
Sándor