chocolatecoco chocolatecoco - 1 year ago 129
MySQL Question

Join query in MySQL

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

Answer Source

You should use INNER JOIN. Take a look here:
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