Query from two tables return error

Поиск
Список
Период
Сортировка
От arnaud gaboury
Тема Query from two tables return error
Дата
Msg-id CAK1hC9tDBe5DE=cW+kouH+axt_kdYxtZUThsmYrJz1xsAHNkwQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Query from two tables return error  (arnaud gaboury <arnaud.gaboury@gmail.com>)
Re: Query from two tables return error  (Sándor Daku <daku.sandor@gmail.com>)
Список pgsql-general
Дерево обсуждения
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 ?

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Create trigger on Materialized View?
Следующее
От: arnaud gaboury
Дата:
Сообщение: Re: Query from two tables return error