From: | arnaud gaboury <arnaud(dot)gaboury(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Query from two tables return error |
Date: | 2016-04-01 09:33:36 |
Message-ID: | CAK1hC9tDBe5DE=cW+kouH+axt_kdYxtZUThsmYrJz1xsAHNkwQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 ?
--
google.com/+arnaudgabourygabx
<https://plus.google.com/_/notifications/emlink?emr=05814804238976922326&emid=CKiv-v6PvboCFcfoQgod6msAAA&path=%2F116159236040461325607%2Fop%2Fu&dt=1383086841306&ub=50>
From | Date | Subject | |
---|---|---|---|
Next Message | arnaud gaboury | 2016-04-01 10:15:40 | Re: Query from two tables return error |
Previous Message | Adrian Klaver | 2016-03-31 21:28:33 | Re: Create trigger on Materialized View? |