带括号的FROM子句的查询中出现语法错误(缺少运算符)
本文关键字:错误 语法 运算符 FROM 子句 查询 | 更新日期: 2023-09-27 18:08:29
我正在使用asp。NET和C#的访问数据库来创建web应用程序。
我目前的问题是SQL语句。在这个项目中,我已经成功地使用了一个字符串值来构建和存储一个查询。因为它存储在一个字符串中,所以看起来有点难看。问题是我写的最后一个查询。它有两个内部连接,我不确定到底出了什么问题。我得到的错误是一个语法错误(缺少运算符(,然后它会列出括号内的所有内容。以下是查询:
SELECT Employee.[First Name], Employee.[Last Name], Employee.[Email],
Departments.[Department]
FROM (
Employee INNER JOIN EmpDept ON Employee.[EmpUserName] = EmpDept.[EmpUserName]
INNER JOIN Departments ON Departments.[Department Number] = '2'
)
WHERE Departments.[Campus]='Clarion';
我知道,这样很难看。。我希望这是一个语法错误,不会太难。
此查询旨在返回员工的姓名、电子邮件和部门。2是用c#代码给出的,在代码的早期确定,但它代表某个部门。empDept表位于Departments表和employee表之间,因此一名员工可以在多个部门中。
非常感谢您的帮助。感谢
只需删除括号。它们迫使数据库尝试将整个表达式视为一个表,这是不对的。
此外,根据您的描述,我会编写查询,将联接中的部门与EmpDept表中的部门相匹配,然后使用WHERE子句向下筛选到dept'2'。现在,您将Department表筛选到部门"2",但将其与查询的其余部分无条件关联。这意味着您从任何部门提取员工记录。
最后,我认为养成使用表别名的习惯是一种很好的做法。它不仅缩短了代码,而且更高级的查询通常会从同一个表的多个实例中提取,而别名使它明确了您所指的表的哪个实例。
SELECT e.[First Name], e.[Last Name], e.[Email], d.[Department]
FROM Employee e
INNER JOIN EmpDept ed ON e.[EmpUserName] = ed.[EmpUserName]
INNER JOIN Departments d ON d.[Department Number] = ed.[Deptartment Number]
WHERE d.[Campus]='Clarion' AND d.[Department Number] = '2';
试试这个
SELECT Employee.[First Name], Employee.[Last Name],
Employee.[Email], Departments.[Department]
FROM Employee
INNER JOIN EmpDept ON Employee.[EmpUserName] = EmpDept.[EmpUserName]
INNER JOIN Departments ON EmpDept.[DepartmentId] = Departments.[Id]
WHERE Departments.[Campus]='Clarion'
AND Departments.[Department Number] = '2'
您需要EmpDept表上与Departments表匹配的ID。
SELECT Employee.[First Name], Employee.[Last Name], Employee.[Email], Departments.[Department]
FROM Employee
INNER JOIN EmpDept ON Employee.[EmpUserName] = EmpDept.[EmpUserName]
INNER JOIN Departments ON Departments.[Department Number] = Employee.[Department Number]
WHERE Departments.[Campus]='Clarion' and Departments.[Department Number]=2
squery中有一个小语法错误,最好使用列名连接表,并在where子句中提及列值条件。。
Access如果包含多个联接,则需要在FROM
子句中使用括号。作为第一步,请在Access查询设计器中尝试类似的查询。
SELECT
Employee.[First Name],
Employee.[Last Name],
Employee.[Email],
Departments.[Department]
FROM
(Employee
INNER JOIN EmpDept
ON Employee.[EmpUserName] = EmpDept.[EmpUserName])
INNER JOIN Departments
ON Departments.[Department Number] = '2'
WHERE Departments.[Campus]='Clarion';
我想我把括号放对了;您可以在查询设计器中进行确认。然而,我对第二个ON
子句感到困惑。
ON Departments.[Department Number] = '2'
该子句不引用联接的"left">侧的任何字段。我不明白它应该实现什么,我也不确定数据库引擎是否会在那里实现你想要的。
试试这个:
SELECT Employee.[First Name], Employee.[Last Name], Employee.[Email],
Departments.[Department]
FROM Employee
INNER JOIN EmpDept ON Employee.[EmpUserName] = EmpDept.[EmpUserName]
INNER JOIN Departments ON EmpDept.[Department Number] = Departments.[Department Number]
/* Or whatever your foreign key between Departments and EmpDept is */
WHERE Departments.[Department Number] = '2'
AND Departments.[Campus] = 'Clarion'
如果你一心想要一个子查询,你需要对它进行别名,并确保它自己形成一个完整的查询:
SELECT e.[First Name], e.[Last Name], e.Email, e.Department
FROM
(
SELECT Employee.[First Name], Employee.[Last Name], Employee.[Email],
Departments.[Department], Departments.Campus
FROM Employee
INNER JOIN EmpDept ON Employee.[EmpUserName] = EmpDept.[EmpUserName]
INNER JOIN Departments ON EmpDept.[Department Number] = Departments.[Department Number]
AND Departments.[Department Number] = '2'
) AS e
WHERE e.Campus = 'Clarion'