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.gabo...@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 r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
