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 '='
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