sql - 如何使用 SQL 连接来自两个不同表的信息

Question

我正在尝试从两个不同的表返回数据，并过滤掉一些返回的数据。我有一个发布者表和一个作者表。我收到一个错误：

MySQL 编号：1064 MySQL 错误：您的 SQL 语法有错误；查看与您的 MySQL 服务器版本相对应的手册，了解在 'IL' AND p.state = 'IL' ORDER BY p.name, p.city, a.name, a.city, a.state' 附近使用的正确语法在第 4 行 SQL: SELECT p.name, p.city, a.name, a.city, a.state FROM publisher p JOIN author a ON (p.state= a.state) WHERE a.city = 'Chicago AND a .state = 'IL' AND p.state = 'IL' ORDER BY p.name, p.city, a.name, a.city, a.state MySQL 受影响的行数：-1

SQL

 SELECT p.name, p.city, a.name, a.city, a.state
FROM publisher p JOIN author a
            ON (p.state= a.state)
WHERE  a.city = 'Chicago' AND a.state = 'IL' AND  p.state = 'IL'
ORDER BY  p.name, p.city, a.name, a.city, a.state;

标签： sql

Answer 1

加入状态没有必要做 a.state = 'IL' AND p.state = 'IL'。尝试做这样的事情：

SELECT P.NAME, P.CITY, A.NAME, A.CITY, A.STATE
FROM PUBLISHER P, AUTHOR A
WHERE A.STATE = P.STATE 
AND A.CITY = "Chicago"
AND A.STATE = "IL"
ORDER BY P.NAME, P.CITY, A.NAME, A.CITY, A.STATE