急死了不知道是哪出错了...

[zbbaby]

我用的是ACCESS数据库3张表分别是Employe,Department,Comany ,vs2005在做个员工管理的系统第一次用,看Scott Mitchell的ASP.NET2.0数据指南这个书,在2005里面创建强类型的DataSet和Table Adapter
写上了个SQL语句

SELECT [Employe_ID], [Employe_Name], [Department_ID], [Employe_Position],
[Employe_Email], [Employe_Mobile], [Employe_Phone], [Employe__QQ],
[Employe_MSN], [Employe_State],
(SELECT Department_Name
FROM Department
WHERE Department.Department_ID = Employe.Department_ID)
AS Department_Name
FROM Employe
提示说SELECT 子句错误: “SELECT”附近的表达式。
SELECT 子句错误: “FROM”附近的表达式。
缺少 FROM 子句。
无法分析查询文本。

我要是在在下面加个SELECT [Employe_ID], [Employe_Name], [Department_ID], [Employe_Position],
[Employe_Email], [Employe_Mobile], [Employe_Phone], [Employe__QQ],
[Employe_MSN], [Employe_State],
(SELECT Department_Name
FROM Department
WHERE Department.Department_ID = Employe.Department_ID)
AS Department_Name
FROM Employe
where Department_ID=@Department_ID
他就会说至少有一个变量未被指定值


请问这是怎么回事啊...,,,,,我急.......急死了

[jimu8130]

你这个是属于多表连接查询
select × from tablea a left join tableb b on a.uid = b.id;

[cfreez]

用inner join
@Department_ID这个变量

[konye]

SELECT a.[Employe_ID], a.[Employe_Name], a.[Department_ID], a.[Employe_Position],
a.[Employe_Email], a.[Employe_Mobile], a.[Employe_Phone], a.[Employe__QQ],
a.[Employe_MSN], a.[Employe_State], b.Department_Name
FROM Employe a,Department b
where a.Department_ID = b.Department_ID

这样试试

[jimu8130]

SELECT a.[Employe_ID], a.[Employe_Name], a.[Department_ID……, b.Department_Name FROM Department b left join Employe a on a.Department_ID = b.Department_ID where
Department_ID=@Department_ID

这个sql语句因为没有指定partment_ID具体是什么值，所以你在外面程序里面要新建一个sqlparameter，并赋值

[amandag]

SELECT Department_Name
FROM Department
WHERE Department.Department_ID = Employe.Department_ID

你这个子查询可能会被认为返回多条记录

[zbbaby]

那为什么Scott Mitchell的ASP.NET2.0数据指南书上连的Northwind数据库就可以

第五步：完成数据访问层
注意，ProductsTableAdapters类从Products表中返回的 是CategoryID和SupplierID的值，但并不包括Categories表 的CategoryName字段和Suppliers表的CompanyName字段，尽管当 我们显示产品信息时，这些很可能是我们想要显示的字段。我们可以扩充TableAdapter的起始方 法GetProducts()来包含CategoryName和CompanyName字段的值， 这方法进而会更新强类型的DataTable来包括这些新的字段。

但这会造成一个问题，因为TableAdapter的插入，更新，删除数据的方法是基于这个起始方法的，幸运的是， 自动生成的插入，更新，删除方法并不会受SELECT子句中的子查询的影响。如果我们注意把 对Categories和Suppliers的查询添加成子查询，而不是用JOIN语 句的话，我们可以避免重做这些修改数据的方法。在ProductsTableAdapter中的GetProducts()方法上按右鼠标，选择“配置”，然后，把SELECT子句改成：

SQL

SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
(SELECT CategoryName FROM Categories
WHERE Categories.CategoryID = Products.CategoryID) as CategoryName,
(SELECT CompanyName FROM Suppliers
WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName
FROM Products