gistfile1.txt

CREATE `users_view` AS
select coalesce(`wp_watupro_groups`.`name`, 'NONE') AS `user_group`,
  `wp_users`.`ID` AS `user_id`,
  (
    select `wp_usermeta`.`meta_value`
    from `wp_usermeta`
    where (
        (`wp_usermeta`.`meta_key` = 'first_name')
        and (`wp_usermeta`.`user_id` = `wp_users`.`ID`)
      )
  ) AS `fname`,
  (
    select `wp_usermeta`.`meta_value`
    from `wp_usermeta`
    where (
        (`wp_usermeta`.`meta_key` = 'last_name')
        and (`wp_usermeta`.`user_id` = `wp_users`.`ID`)
      )
  ) AS `lname`,
  `wp_users`.`user_login` AS `email`,
  `wp_users`.`user_registered` AS `user_registered`,
(
    select max(`wp_usermeta`.`meta_value`)
    from `wp_usermeta`
    where (
        (`wp_usermeta`.`meta_key` = 'last_location_city')
        and (`wp_usermeta`.`user_id` = `wp_users`.`ID`)
      )
  ) AS `location_city`,
  (
    select max(`wp_usermeta`.`meta_value`)
    from `wp_usermeta`
    where (
        (
          `wp_usermeta`.`meta_key` = 'last_location_country'
        )
        and (`wp_usermeta`.`user_id` = `wp_users`.`ID`)
      )
  ) AS `location_country`
from (
    (
      `wp_users`
      join `wp_usermeta` on(
        (
          (`wp_users`.`ID` = `wp_usermeta`.`user_id`)
          and (`wp_usermeta`.`meta_key` = 'watupro_groups')
        )
      )
    )
    left join `wp_watupro_groups` on(
      (
        trim(
          both '"'
          from substring_index(
              substring_index(`wp_usermeta`.`meta_value`, ';', 2),
              ':',
              -(1)
            )
        ) = `wp_watupro_groups`.`ID`
      )
    )
  );