Re: Query from two tables return error

On Fri, Apr 1, 2016 at 3:33 PM, Sándor Daku <daku(dot)sandor(at)gmail(dot)com> wrote:

>
> 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.
>
>
> You can simply redefine the view.
>
> SELECT *,((mailusers.domain_name || '/'::text) || mailusers.username) ||
> '/'::text AS home_dir
> FROM email.mailusers;
>

Thank you so much. This way I get all needed info in one view.

>
> 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
>
>
>

--

google.com/+arnaudgabourygabx
<https://plus.google.com/_/notifications/emlink?emr=05814804238976922326&emid=CKiv-v6PvboCFcfoQgod6msAAA&path=%2F116159236040461325607%2Fop%2Fu&dt=1383086841306&ub=50>