MySQL 中的连接查询

发布于 2024-09-15 10:51:09 字数 583 浏览 1 评论 0原文

我有两个表：members 和 renews

这两个表有一个名为 memberid 的字段，它将两个数据表链接在一起。我正在尝试构建一个查询，该查询将从成员表中提取名字和姓氏，以获取续订表中保存的数据。我尝试使用下面的查询，该查询基于我查找的一些示例。

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

尝试在 phpMyAdmin 中运行此命令时出现错误：

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

alt text

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦回梦里 2024-09-22 10:51:09

您应该使用INNER JOIN。看一下这里： http://www.postgresql.org /docs/8.1/interactive/queries-table-expressions.html
即使是针对 PostgreSQL，查询对于 MySQL 来说也是一样的，而且示例也非常好。

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

或者您可以使用 USING (memberid) 而不是 ON Members.memberid =renewals.memberid

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

回复收藏 0 原文

七婞 2024-09-22 10:51:09

如果您想在单列中显示会员姓名，请稍加修改

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

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

回复收藏 0 原文

~没有更多了~