Re: query from two tables & concat the result

On Wed, Feb 3, 2016 at 8:19 PM, Raymond O'Donnell <rod(at)iol(dot)ie> 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(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)

Very good. I changed tables accordingly. No more the id columns now.
Thank you so much.

> ...
>
>
> HTH,
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod(at)iol(dot)ie

--

google.com/+arnaudgabourygabx