Federated table of two LEFT JOIN tables not working

For some reasons I am getting the following error when executing the below query:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=FEDERATED' at line 35

CREATE TABLE users AS (
  SELECT
    ID, user_email, user_registered,
    first_name.meta_value as first_name,
    last_name.meta_value as last_name,
    telephone.meta_value as telephone,
    country.meta_value as country,
    company.meta_value as company,
    address.meta_value as address,
    city.meta_value as city,
    professional_title.meta_value as professional_title,
    state.meta_value as state,
    areas_of_interest.meta_value as areas_of_interest
    FROM wp_users
    LEFT JOIN wp_usermeta AS first_name ON first_name.user_id=ID
        AND first_name.meta_key='first_name'
    LEFT JOIN wp_usermeta AS last_name ON last_name.user_id=ID
        AND last_name.meta_key='last_name'
    LEFT JOIN wp_usermeta AS telephone ON telephone.user_id=ID
        AND telephone.meta_key='telephone'
    LEFT JOIN wp_usermeta AS country ON country.user_id=ID
        AND country.meta_key='country'
    LEFT JOIN wp_usermeta AS company ON company.user_id=ID
        AND company.meta_key='company'
    LEFT JOIN wp_usermeta AS address ON address.user_id=ID
        AND address.meta_key='address'
    LEFT JOIN wp_usermeta AS city ON city.user_id=ID
        AND city.meta_key='city'
    LEFT JOIN wp_usermeta AS professional_title ON professional_title.user_id=ID
        AND professional_title.meta_key='professional_title'
    LEFT JOIN wp_usermeta AS state ON state.user_id=ID
        AND state.meta_key='state'
    LEFT JOIN wp_usermeta AS areas_of_interest ON areas_of_interest.user_id=ID
        AND areas_of_interest.meta_key='areas_of_interest'
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION "*****";

Note: FEDERATED Engine is on and working on other non-joined table.

Solution

Short answer:
CREATE statements for FEDERATED tables require an additional piece of data called the CONNECTION. Not having that data in your statement is the source of the syntax error.

Long answer:

For this particular instance, a VIEW would be a better tool than a FEDERATED table.

The FEDERATED storage engine can be though of kind of like symbolic linking in Linux or shortcuts in Windows. It is primarily used to access a table on a server that doesn't actually store the table locally, but on a different mysql server. For that reason, when you create a FEDERATED table, you also have to include the CONNECTION that the FEDERATED storage engine should use in the CREATE statement. You can opt to create a FEDERATED table on the same host that the data is stored, but it'll result in additional overhead caused by the unnecessary steps taken to access local data. Please see this link for more details.

A VIEW, on the other hand, is like a stored query that executes whenever you query the VIEW table. This page can give you more information on it.