Re: query from two tables & concat the result

On 03/02/2016 14:05, arnaud gaboury wrote:
>>>
>>> thetradinghall=> SELECT u.username ||'@'||d.domain as email_address
>>> FROM email.mailusers u
>>> INNER JOIN
>>> email.domainlist d
>>> ON
>>> (u.domain_id=d.id);
>>>
>>> email_address
>>> -----------------------------------
>>> arnaud(dot)gaboury(at)thetradinghall(dot)com
>>> (1 row)
>>> -------------------------------------
>>>
>>> As for the cleaning of ID, I dropped id and changed both primary keys.
>>> Thank you so much for your prompt answer and help.
>
> In fact I kept the id for table domainlist (changed the name
> accordingly your advise). If I remove the id column, I will not be
> able anymore to do the above SELECT , no?
> The condition (u.domain_id=d.id) will no more be possible.
>
> Am I wrong?

You're right - you'll need to use the domain name as the foreign key
instead. So your tables will look like this:

CREATE TABLE domains (
domain_name text not null primary key,
....
);

CREATE TABLE mailusers (
username text not null,
password text not null,
domain_name text not null,
created timestamp with time zone not null default now(),
....
constraint users_pk primary key (username, domain_name),
constraint users_domains_fk foreign key (domain_name)
references domains(domain_name)
);

And then your query would look something like this:

select u.username ||'@'||d.domain as email_address
from mailusers u
inner join domains d on (u.domain_name = d.domain_name)
...

HTH,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie