0

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

0

Apparently both wattp_users and wattp2_as_score tables have a column named score_as.

Put a prefix with the table name before every mention to score_as column, just as you did with the ID (e.g. wattp2_users.ID) to avoid beeing ambiguous.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.