I have two data tables.

Table 1
Sender and Recipient
- sender_email_address
- recipient_email_address

Table 2
Email_ID_Map
-email_id
-email_address

All the sender_email_address(es) and recipient_email_address(es) in
Table 1 can be found in Table 2.

How to write SQL to create a view such that all the email addresses in
Table 1 are replaced with their corresponding email ids?

Thanks
Roy

On 4 Sep, 05:19, Roy <royl...@hotmail.com> wrote:
> I have two data tables.
>
> Table 1
> Sender and Recipient
> - sender_email_address
> - recipient_email_address
>
> Table 2
> Email_ID_Map
> -email_id
> -email_address
>
> All the sender_email_address(es) and recipient_email_address(es) in
> Table 1 can be found in Table 2.
>
> How to write SQL to create a view such that all the email addresses in
> Table 1 are replaced with their corresponding email ids?
>
> Thanks
> Roy

What have you got so far?

On Sep 3, 11:48 pm, strawberry <zac.ca...@gmail.com> wrote:
> On 4 Sep, 05:19, Roy <royl...@hotmail.com> wrote:
>
>
>
>
>
> > I have two data tables.
>
> > Table 1
> > Sender and Recipient
> > - sender_email_address
> > - recipient_email_address
>
> > Table 2
> > Email_ID_Map
> > -email_id
> > -email_address
>
> > All the sender_email_address(es) and recipient_email_address(es) in
> > Table 1 can be found in Table 2.
>
> > How to write SQL to create a view such that all the email addresses in
> > Table 1 are replaced with their corresponding email ids?
>
> > Thanks
> > Roy
>
> What have you got so far?- Hide quoted text -
>
> - Show quoted text -

Nothing much yet.

On Sep 4, 12:43 am, Roy <royl...@hotmail.com> wrote:
> On Sep 3, 11:48 pm, strawberry <zac.ca...@gmail.com> wrote:
>
>
>
>
>
> > On 4 Sep, 05:19, Roy <royl...@hotmail.com> wrote:
>
> > > I have two data tables.
>
> > > Table 1
> > > Sender and Recipient
> > > - sender_email_address
> > > - recipient_email_address
>
> > > Table 2
> > > Email_ID_Map
> > > -email_id
> > > -email_address
>
> > > All the sender_email_address(es) and recipient_email_address(es) in
> > > Table 1 can be found in Table 2.
>
> > > How to write SQL to create a view such that all the email addresses in
> > > Table 1 are replaced with their corresponding email ids?
>
> > > Thanks
> > > Roy
>
> > What have you got so far?- Hide quoted text -
>
> > - Show quoted text -
>
> Nothing much yet.- Hide quoted text -
>
> - Show quoted text -

OK, here is the solution.

create or replace view EMAIL_ALL as select a.sender_email_address,
b.email_id, a.recipient_email_address, c.email_id from
sender_and_recipient a, email_id_map b , email_id_map c where
a.sender_email_address = b.email_address and a.recipient_email_address
= c.email_address

On 4 Sep, 09:00, Roy <royl...@hotmail.com> wrote:
> On Sep 4, 12:43 am, Roy <royl...@hotmail.com> wrote:
>
>
>
>
>
> > On Sep 3, 11:48 pm, strawberry <zac.ca...@gmail.com> wrote:
>
> > > On 4 Sep, 05:19, Roy <royl...@hotmail.com> wrote:
>
> > > > I have two data tables.
>
> > > > Table 1
> > > > Sender and Recipient
> > > > - sender_email_address
> > > > - recipient_email_address
>
> > > > Table 2
> > > > Email_ID_Map
> > > > -email_id
> > > > -email_address
>
> > > > All the sender_email_address(es) and recipient_email_address(es) in
> > > > Table 1 can be found in Table 2.
>
> > > > How to write SQL to create a view such that all the email addresses in
> > > > Table 1 are replaced with their corresponding email ids?
>
> > > > Thanks
> > > > Roy
>
> > > What have you got so far?- Hide quoted text -
>
> > > - Show quoted text -
>
> > Nothing much yet.- Hide quoted text -
>
> > - Show quoted text -
>
> OK, here is the solution.
>
> create or replace view EMAIL_ALL as select a.sender_email_address,
> b.email_id, a.recipient_email_address, c.email_id from
> sender_and_recipient a, email_id_map b , email_id_map c where
> a.sender_email_address = b.email_address and a.recipient_email_address
> = c.email_address- Hide quoted text -
>
> - Show quoted text -

Better to write as explicit JOINs with obvious JOIN criteria rather
than comma joins where the criteria for each join is clear, thus:

CREATE OR REPLACE VIEW `EMAIL_ALL` AS
SELECT
`a`.`sender_email_address`,
`b`.`email_id`,
`a`.`recipient_email_address`,
`c`.`email_id`
FROM `sender_and_recipient` `a`
JOIN `email_id_map` `b` ON `a`.`sender_email_address` =
`b`.`email_address`
JOIN `email_id_map` `c` ON `a`.`recipient_email_address` =
`c`.`email_address`

On Sep 4, 1:48 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 4 Sep, 09:00, Roy <royl...@hotmail.com> wrote:
>
>
>
>
>
> > On Sep 4, 12:43 am, Roy <royl...@hotmail.com> wrote:
>
> > > On Sep 3, 11:48 pm, strawberry <zac.ca...@gmail.com> wrote:
>
> > > > On 4 Sep, 05:19, Roy <royl...@hotmail.com> wrote:
>
> > > > > I have two data tables.
>
> > > > > Table 1
> > > > > Sender and Recipient
> > > > > - sender_email_address
> > > > > - recipient_email_address
>
> > > > > Table 2
> > > > > Email_ID_Map
> > > > > -email_id
> > > > > -email_address
>
> > > > > All the sender_email_address(es) and recipient_email_address(es) in
> > > > > Table 1 can be found in Table 2.
>
> > > > > How to write SQL to create a view such that all the email addresses in
> > > > > Table 1 are replaced with their corresponding email ids?
>
> > > > > Thanks
> > > > > Roy
>
> > > > What have you got so far?- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > Nothing much yet.- Hide quoted text -
>
> > > - Show quoted text -
>
> > OK, here is the solution.
>
> > create or replace view EMAIL_ALL as select a.sender_email_address,
> > b.email_id, a.recipient_email_address, c.email_id from
> > sender_and_recipient a, email_id_map b , email_id_map c where
> > a.sender_email_address = b.email_address and a.recipient_email_address
> > = c.email_address- Hide quoted text -
>
> > - Show quoted text -
>
> Better to write as explicit JOINs with obvious JOIN criteria rather
> than comma joins where the criteria for each join is clear, thus:
>
> CREATE OR REPLACE VIEW `EMAIL_ALL` AS
> SELECT
> `a`.`sender_email_address`,
> `b`.`email_id`,
> `a`.`recipient_email_address`,
> `c`.`email_id`
> FROM `sender_and_recipient` `a`
> JOIN `email_id_map` `b` ON `a`.`sender_email_address` =
> `b`.`email_address`
> JOIN `email_id_map` `c` ON `a`.`recipient_email_address` =
> `c`.`email_address`- Hide quoted text -
>
> - Show quoted text -

Thank you very much.
Roy