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.gaboury@thetradinghall.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@iol.ie