Creating one view from two

[galorin]

I have the following two views, but so far, every effort to combine the two has resulted in SQL errors.

DROP VIEW IF EXISTS `contacts`.`ActiveUsers`;
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`remote-root`@``
SQL SECURITY DEFINER VIEW `ActiveUsers` AS
select
  `p`.`First Name` AS `First Name`
, `p`.`Last Name` AS `Last Name`
, `p`.`Email` AS `Email`
, `p`.`Direct Dial` AS `PerPhone`
, `p`.`Company` AS `Company`
, `u`.`Visits` AS `Visits`
from (`people` `p`
left join `Users1` `u` on((`p`.`Email` = `u`.`Email`)))
where (`p`.`Email` = `u`.`Email`)
order by `u`.`Visits` desc;

DROP VIEW IF EXISTS `contacts`.`FullActiveUsers`;
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`remote-root`@``
SQL SECURITY DEFINER VIEW `FullActiveUsers` AS
select distinct
a.`First Name` AS `First Name`,
a.`Last Name` AS `Last Name`,
a.`PerPhone` AS `PerPhone`,
a.`Company` AS `Company`,
c.`Telephone` AS `CoPhone`,
c.`Town` AS `Town`,
c.`County` AS `County`
from (`ActiveUsers` as a
left join `companies` as c
on((a.`Company` = c.`Company`)))
order by a.`Visits` desc;

I had to modify both to provide phone numbers for both people and companies for a php pdf report generator.

The Users1 table referenced a date-restricted view of a table containing website activity.

Is it possible to combine these two views and make them more efficient?

 

[r937]

select p.`First Name`
     , p.`Last Name`
     , p.`Email`
     , p.`Direct Dial`  AS PerPhone
     , p.`Company`      AS Company
     , null             AS CoPhone
     , null             AS Town
     , null             AS County
     , u.`Visits`       
  from people AS p
left outer
  join Users1 AS u
    on u.Email = p.Email
union
select a.`First Name`
     , a.`Last Name`
     , null
     , a.`PerPhone`
     , a.`Company`
     , c.`Telephone` 
     , c.`Town`
     , c.`County`
     , null
  from ActiveUsers as a
left outer
  join companies as c
    on c.Company = a.Company

the ORDER BY should not be included in the view

r937.com | rudy.ca