3

I have two tables: members and renewals

The two tables have a field called memberid which links the two data tables together. I am trying to construct a query which will extract the firstname and surname from the members table for the data that is being held in the renewals table. I have tried using the query below which is based on some examples I've looked up.

SELECT members.memberfirst, members.membersurname, members.memberid, renewals.account_name, renewals.memberid 
FROM members, renewals 
WHERE renewals.memberid=members.memberid

The error I get when trying to run this in phpMyAdmin:

#1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

alt text

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
thisisready
  • 623
  • 2
  • 10
  • 22

2 Answers2

2

You should use INNER JOIN. Take a look here: http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html
Even if it's for PostgreSQL, the queries are the same for MySQL and the examples are really good.

SELECT members.memberfirst, members.membersurname, members.memberid, renewals.account_name
FROM members
INNER JOIN renewals
ON members.memberid = renewals.memberid

or you can use USING (memberid) instead of ON members.memberid = renewals.memberid

Alex
  • 5,565
  • 6
  • 36
  • 57
  • Thanks for the suggestion. However, I'm still getting the same error message when I run it: #1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' I've updated the original post with a screenshot from phpmyadmin of the two tables. – thisisready Aug 25 '10 at 11:30
0

A slight modification if you wanted to display the member's name in a single column

SELECT CONCAT(members.memberfirst, " ", members.membersurname) as memberName, 
members.memberid, renewals.account_name, renewals.memberid 
FROM members
INNER JOIN renewals
WHERE renewals.memberid=members.memberid