Thread: Query from two tables return error

Query from two tables return error

From

arnaud gaboury

Date:

01 April 2016, 12:33:41

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

Re: Query from two tables return error

From

arnaud gaboury

Date:

01 April 2016, 13:15:46

On Fri, Apr 1, 2016 at 11:33 AM, arnaud gaboury <arnaud.gaboury@gmail.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.

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 ?

EDIT : I found a solution with creating a new view this way:

SELECT domain_name||'/'||username||'/' AS home_dir,username
FROM email.mailusers;

              home_dir              |    username
------------------------------------+----------------
thetradinghall.com/arnaud.gaboury/ | arnaud.gaboury
thetradinghall.com/admin/          | admin
thetradinghall.com/postmaster/     | postmaster

Now I can pick up the home_dir for username u.

Re: Query from two tables return error

From

Sándor Daku

Date:

01 April 2016, 16:33:48

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

Re: Query from two tables return error

From

arnaud gaboury

Date:

01 April 2016, 16:52:27

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

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

Re: Query from two tables return error

From

Adrian Klaver

Date:

01 April 2016, 17:04:24

On 04/01/2016 06:52 AM, arnaud gaboury wrote:
>
>
> On Fri, Apr 1, 2016 at 3:33 PM, Sándor Daku <daku.sandor@gmail.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.

This might help understand what is going on:

http://www.postgresql.org/docs/9.5/interactive/tutorial-join.html

>
>
>     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.klaver@aklaver.com

Re: Query from two tables return error

From

arnaud gaboury

Date:

01 April 2016, 17:17:02

On Fri, Apr 1, 2016 at 4:04 PM, Adrian Klaver <adrian.klaver@aklaver.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.sandor@gmail.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.klaver@aklaver.com

google.com/+arnaudgabourygabx