Re: Query from two tables return error

On Fri, Apr 1, 2016 at 4:04 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 04/01/2016 06:52 AM, arnaud gaboury wrote:
>
>>
>>
>> On Fri, Apr 1, 2016 at 3:33 PM, Sándor Daku <daku(dot)sandor(at)gmail(dot)com
>>
>
>
>
>> 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.
>>
>
I even added a new column in my view to catch the email address:

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

Now, I am wondering if there is a best practice between :
- one large and complete table
- a lighter table with some views

>
> This might help understand what is going on:
>
> http://www.postgresql.org/docs/9.5/interactive/tutorial-join.html

Thank you for the link.

>
>
>
>>
>> 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
>> >
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--

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