权宜之计002：晕了吧唧的OUTER JOIN

Technorati 标签: outer join, Jet SQL, ACCESS, 参数查询

版权声明：可以任意转载，转载时请务必以超链接形式标明如下文章原始出处和作者信息及本声明

作者：xixi

出处：http://blog.csdn.net/slowgrace/archive/2008/10/05/3016635.aspx

 

话说我在ACCESS中写了个这样的嵌套join：

1. SELECT Sum(tblRecipe_Food.sngNutQuant) AS sngNutQuant之总计, tblNutStan.IDnutStan, tblNutStan.txtNutName
2. FROM tblMeal INNER JOIN (tblRecipe_Food INNER JOIN (tblFood RIGHT JOIN tblNutStan
3. ON tblFood.lngNutTypeId = tblNutStan.IDnutStan)
4. ON tblRecipe_Food.lngFoodId = tblFood.IDfood)
5. ON tblMeal.lngRecipeId = tblRecipe_Food.lngRecipeID 
6. GROUP BY tblNutStan.IDnutStan, tblNutStan.txtNutName, tblMeal.dateMeal
7. HAVING (((tblMeal.dateMeal)=#10/6/2008#));

点击数据表视图，报错提示“不支持连接表达式”。如果把最后的right join改成 inner join，就不报错。问题是我想要把tblNutStan表里的所有记录都检索出来，不管是否在tblFood里是否有相关联的记录，所以我还必须得用RIGHT JOIN.

上面的SQL语句一堆不好看，我把ACCESS里对应的设计视图贴在上面，注意左边2个表之间的箭头方向表示了一个右链接。这个视图上再点击数据表视图，报错：“由于SQL语句中含有二义性的外部连接，所以不能执行。若先强迫执行其中之一个连接，请再创建一个的查询执行第一个连接，然后将该查询包含在SQL语句中。”

看了这个提示，我决定拆成2个查询试试，先写一个这样的查询：

1. SELECT tblFood.lngNutTypeId, tblRecipe_Food.sngNutQuant
2. FROM tblMeal INNER JOIN (tblRecipe_Food INNER JOIN tblFood ON tblRecipe_Food.lngFoodId=tblFood.IDfood) ON tblMeal.lngRecipeId=tblRecipe_Food.lngRecipeID
3. WHERE (((tblMeal.dateMeal)=#10/7/2008#));

命名为zzqryDayPrmFoodNutQuan。对应的设计视图是这样的：

然后基于这个查询再做outer join：

1. SELECT tblNutStan.txtNutName, tblNutStan.lngMin, tblNutStan.lngMax, Sum(zzqryDayPrmFoodNutQuan.sngNutQuant) AS sngNutQuant之总计
2. FROM tblNutStan LEFT JOIN zzqryDayPrmFoodNutQuan ON tblNutStan.IDnutStan = zzqryDayPrmFoodNutQuan.lngNutTypeId
3. GROUP BY tblNutStan.txtNutName, tblNutStan.lngMin, tblNutStan.lngMax, tblNutStan.IDnutStan
4. ORDER BY tblNutStan.IDnutStan;

对应的设计视图是这样的：

这回成了，具体为嘛行了，我也晕了吧唧的。也不是一下就成的，期间试过好几次换子查询的字段，记得主要是总计字段和条件字段换来换去的。这样好了之后，我就高高兴兴的想着怎么把这SQL语句写到代码里。我在代码里，要实时的得到一个日期，替换上面的#2008-10-07#。这可愁坏我了，我不知道该怎么写一个基于动态生成的记录集的查询。因为SELECT语句都是要从数据库里已经存储的对象来找数据的，我估计得先把第一个查询的结果生成一个临时表，然后再left join这个临时表，这些我都不是很熟悉，想想就头大。可能还可以用带参数的存储过程，可我也忘得差不多了。

于是最后我这么做的：我先生成一个动态的recordset，统计得到要outer join的那部分数据；然后把这个记录集的数据（1个字段）逐条复制到要显示所有记录的tblNutStan表的一个字段中；最后用基于tblNutStan表的窗体显示了所有记录。其实我一开始就想这么做，这么做涉及的所有环节我都很熟悉，很快就能折腾出来。只是当时突然冒出一个美好愿望，希望一个查询解决全部问题，就试啊试啊，累死了，最后美好希望还是泡汤了。这会儿忽然想起上学时老师讲join语句的时候我在打瞌睡的事了。唉，搁今天听这课，估计我不会睡哈:~(

P.S. 在Tiger_Zhao的帮助下，最终解决了这个问题。不过，因为程序已经按上面的笨办法测试好了，懒得再折腾了，所以把办法记录在这里。要点包括两个：

1. 在供外连接的那个子查询里，就是zzqryDayPrmFoodNutQuan了，不带参数，就是不把dateMeal作为条件字段，而作为分组字段，这样，zzqryDayPrmFoodNutQuan就不是一个带参数的查询了，而是一个在数据库里存好的不带参数的对象。这个查询实质上是把所有天的记录都查出来了。
2. 在外连接里以zzqryDayPrmFoodNutQuan的dateMeal字段作为left join的连接条件。

现在zzqryDayPrmFoodNutQuan的设计视图是这样的：

SQL语句是这样的：

1. SELECT tblFood.lngNutTypeId, Sum(tblRecipe_Food!sngQuantity*tblFood!sngUnitNutQuant) AS sngNutQuantSum, tblMeal.dateMeal 
2. FROM tblFood INNER JOIN (tblMeal INNER JOIN tblRecipe_Food ON tblMeal.lngRecipeId = tblRecipe_Food.lngRecipeID) ON tblFood.IDfood = tblRecipe_Food.lngFoodId 
3. GROUP BY tblFood.lngNutTypeId, tblMeal.dateMeal;

zzNutAnna的SQL语句是这样的（注意加黑的这一不烦）：

1. SELECT tblNutStan.txtNutName, tblNutStan.lngMin, tblNutStan.lngMax, zzqryDayPrmFoodNutQuan.sngNutQuantSum 
2. FROM tblNutStan LEFT JOIN zzqryDayPrmFoodNutQuan ON ((tblNutStan.IDnutStan = zzqryDayPrmFoodNutQuan.lngNutTypeId) AND (((zzqryDayPrmFoodNutQuan.dateMeal)=#10/9/2008#))) 
3. GROUP BY tblNutStan.txtNutName, tblNutStan.lngMin, tblNutStan.lngMax, zzqryDayPrmFoodNutQuan.sngNutQuantSum, tblNutStan.IDnutStan 
4. ORDER BY tblNutStan.IDnutStan;

zzNutAnna的设计视图不能贴出来，因为虽然它能查询出正确的结果，但是系统说现在这个查询不能在设计视图中显示；而我存盘之后干脆再打开这个查询，效果就更诧异了。它会闪一下正确的查询结果，然后报错“不支持连接表达式”，然后就关闭。所以事实上我现在再也打不开这个查询了，幸亏我先把它拷在这个博文里了。所以，这个办法是否能在代码里奏效，我其实还不是很有谱，以后遇到再说吧。