MySQL内部联接查询语法错误 [英] MySQL Inner Join Query Syntax error
问题描述
我是MySQL的新手,无法弄清楚这里出了什么问题.我有两张桌子.左侧的表格称为锻炼".相关列为date
(类型日期)和id
(类型int).
I'm kind of a MySQL novice and can't figure out what is going wrong here. I have two tables. The left table is called Workouts. The relevant columns are date
(type date) and id
(type int).
右表称为Workout_locations
(相关字段:workout_id type int, and location_id type int
).
The right table is called Workout_locations
(relevant fields: workout_id type int, and location_id type int
).
联接字段为Workouts.id
和Workout_locations.workout_id
.
我要做的就是得到一个包含两列的表:date
(来自Workouts)和location_id
(来自Workout_locations
).我只需要基于几个字段从Workouts
表中提取记录(sql语句应该清楚这一点).
All I want to do is get a table of two columns: date
(from Workouts), and location_id
(from Workout_locations
). I need to only pull records from the Workouts
table based on a couple of fields (the sql statement should make this clear).
这是我的sql语法:
SELECT Workouts.date as date, Workout_locations.location_id as loc_id
FROM Workouts
WHERE Workouts.pacegroup_id='9' AND (Workouts.date BETWEEN '2013-08-19' AND '2013-08-25')
INNER JOIN Workout_locations ON Workouts.id=Workout_locations.workout_id"
但是我得到这个错误:
您的SQL语法有错误;检查手册 对应于您的MySQL服务器版本以使用正确的语法 'INNER JOIN Workout_locations ON附近 第1行的Workouts.id = Workout_locations.workout_id'
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN Workout_locations ON Workouts.id=Workout_locations.workout_id' at line 1
我希望对于有经验的人来说,这是一个非常容易发现的错误.有人可以看到吗?谢谢!
I'm hoping that this is a very easy error to spot for someone who is experienced with this. Can anyone see it? Thanks!
推荐答案
您的INNER JOIN
应该在WHERE
之前.我也不认为您需要在<的 c12>子句,但我怀疑这两种方式都会导致错误:
Your INNER JOIN
should come before the WHERE
. I also don't think you needed the parens around your BETWEEN
clause, but I doubt that it'll cause an error either way:
SELECT Workouts.date as date, Workout_locations.location_id as loc_id
FROM Workouts
INNER JOIN Workout_locations ON Workouts.id=Workout_locations.workout_id
WHERE Workouts.pacegroup_id = '9'
AND Workouts.date BETWEEN '2013-08-19' AND '2013-08-25';
此外,尽管他们从技术上使他们脱颖而出,但您应该避免使用日期"作为选定的列名(这是一个
Also, though they technically let you get away with it, you should avoid using "date" as a selected column name (it's a reserved word).
您还可以进行一些简化以使内容更易于阅读:
You could do a bit of streamlining as well to make things a bit easier to read:
SELECT Workouts.date AS wo_date, Workout_locations.location_id AS loc_id
FROM Workouts w
INNER JOIN Workout_locations l ON w.id = l.workout_id
WHERE w.pacegroup_id = '9'
AND w.date BETWEEN '2013-08-19' AND '2013-08-25';
这篇关于MySQL内部联接查询语法错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!