Appreciate help in advance.
I am dealing with a bit of a pickle with a formula. I am trying to create a page template that will display a list of users, specific metadata from usermeta table + additionally I want to pull data from table created by me. Here is a screenshot of the table structure: https://www.screencast.com/t/X07XDr3Gy.
I got it partially working to get user name, surname and company:
$sql = "
SELECT {$wpdb->users}.ID, firstname.meta_value as first_name, lastname.meta_value as last_name, webaria_company.meta_value as webaria_company,
FROM {$wpdb->users}
INNER JOIN (SELECT user_id, meta_value
FROM {$wpdb->usermeta} WHERE meta_key = 'first_name') as firstname ON {$wpdb->users}.ID = firstname.user_id
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'last_name') as lastname ON {$wpdb->users}.ID = lastname.user_id
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'webaria_company') as webaria_company ON {$wpdb->users}.ID = webaria_company.user_id
";
$asuserlist = $wpdb->get_results($sql);
And that works perfectly
But then I try to get additional information from table attp2_as_score, column score_as
$sql = "
SELECT {$wpdb->users}.ID, firstname.meta_value as first_name, lastname.meta_value as last_name, webaria_company.meta_value as webaria_company, score_as
FROM {$wpdb->users}, wattp2_as_score
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'first_name') as firstname ON {$wpdb->users}.ID = firstname.user_id
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'last_name') as lastname ON {$wpdb->users}.ID = lastname.user_id
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'webaria_company') as webaria_company ON {$wpdb->users}.ID = webaria_company.user_id
INNER JOIN (SELECT score_as FROM wattp2_as_score WHERE approved=1) as scoreas ON {$wpdb->users}.ID = scoreas.user_id
";
$asuserlist = $wpdb->get_results($sql);
And I get error:
WordPress database error: [Column ‘score_as’ in field list is ambiguous]
SELECT wattp2_users.ID, firstname.meta_value as first_name, lastname.meta_value as last_name, webaria_company.meta_value as webaria_company, score_as FROM wattp2_users, wattp2_as_score INNER JOIN (SELECT user_id, meta_value FROM wattp2_usermeta WHERE meta_key = ‘first_name’) as firstname ON wattp2_users.ID = firstname.user_id INNER JOIN (SELECT user_id, meta_value FROM wattp2_usermeta WHERE meta_key = ‘last_name’) as lastname ON wattp2_users.ID = lastname.user_id INNER JOIN (SELECT user_id, meta_value FROM wattp2_usermeta WHERE meta_key = ‘webaria_company’) as webaria_company ON wattp2_users.ID = webaria_company.user_id INNER JOIN (SELECT score_as FROM wattp2_as_score WHERE approved=1) as scoreas ON wattp2_users.ID = scoreas.user_id
