Admire assist upfront.
I’m coping with a little bit of a pickle with a method. I’m attempting to create a web page template that can show a listing of customers, particular metadata from usermeta desk + moreover I wish to pull information from desk created by me. Here’s a screenshot of the desk construction: https://www.screencast.com/t/X07XDr3Gy.
I obtained it partially working to get person title, surname and firm:
$sql = "
SELECT {$wpdb->customers}.ID, firstname.meta_value as first_name, lastname.meta_value as last_name, webaria_company.meta_value as webaria_company,
FROM {$wpdb->customers}
INNER JOIN (SELECT user_id, meta_value
FROM {$wpdb->usermeta} WHERE meta_key = 'first_name') as firstname ON {$wpdb->customers}.ID = firstname.user_id
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'last_name') as lastname ON {$wpdb->customers}.ID = lastname.user_id
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'webaria_company') as webaria_company ON {$wpdb->customers}.ID = webaria_company.user_id
";
$asuserlist = $wpdb->get_results($sql);
And that works completely
However then I attempt to get extra info from desk attp2_as_score, column score_as
$sql = "
SELECT {$wpdb->customers}.ID, firstname.meta_value as first_name, lastname.meta_value as last_name, webaria_company.meta_value as webaria_company, score_as
FROM {$wpdb->customers}, wattp2_as_score
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'first_name') as firstname ON {$wpdb->customers}.ID = firstname.user_id
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'last_name') as lastname ON {$wpdb->customers}.ID = lastname.user_id
INNER JOIN (SELECT user_id, meta_value FROM {$wpdb->usermeta} WHERE meta_key = 'webaria_company') as webaria_company ON {$wpdb->customers}.ID = webaria_company.user_id
INNER JOIN (SELECT score_as FROM wattp2_as_score WHERE authorised=1) as scoreas ON {$wpdb->customers}.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 authorised=1) as scoreas ON wattp2_users.ID = scoreas.user_id