On Wed, Feb 3, 2016 at 8:19 PM, Raymond O'Donnell <rod@xxxxxx> wrote: > 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@xxxxxxxxxxxxxxxxxx >>>> (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) Very good. I changed tables accordingly. No more the id columns now. Thank you so much. > ... > > > HTH, > > Ray. > > -- > Raymond O'Donnell :: Galway :: Ireland > rod@xxxxxx -- google.com/+arnaudgabourygabx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
