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