求教改写一条sql

[dairy1]

] from [item specification] inner join pricequery on [item specification].[product reference] = pricequery.productref where [item specification].[order ref] = form中输入的值 group by price query.brand,pricequery.linename,pricequery.productref,pricequery.description,pricequery.buyingprice,pricequery.[public price]
我是想用一句sql语句在vb中实现，最好不用存储过程什么的。所以我改写如下：
select brand,linename,productref,description,ordered_items_bak.[buying price],ordered_items_bak.[public price] from orders join ordered_items_bak on orders.[order ref] = ordered_items_bak.[order ref] join [product information] on [product information].[productref] = ordered_items_bak.[product reference] join item_specification on ordered_items_bak.[product reference] = item_specification.[product reference] and ordered_items_bak.[order ref] = item_specification.[order ref] where item_specification.[order ref] = 'Sp0101123186' group by [orders].brand,[product information].linename,[product information].productref,[product information].description,[public price],[public price2],[public price3],[buying price],ordered_items_bak.[order ref] order by orders.brand,[product information].linename,[product information].productref
结果发现 buying price 与public price两项都是null没有数字。表中是有数字的啊
怎么写法最简单呢
我改写的中'sp0101123186'就是from中输入的值。两项没有数字。
但是如果去掉这个where查整个表就有数字。奇怪，有人能帮我吗。怎么写
orders表的字段
brand
[order ref]
...
producet information表的字段
product ref
description
linename
...
ordered_items_bak表的字段
order ref
product ref
buying price
public price

itemspecification表
order ref
product ref
size
color
quantity


根据上述列出来的order ref与product ref进行关联
要得到输入一个order ref号查到
brand,linename,productref,description,ordered_items_bak.[buying price],ordered_items_bak.[public price]

[zjcxc]

太长了.

[yoki]

晕~

[Rotaxe]

检查一下数据，ordered_items_bak.[buying price]和ordered_items_bak.[public price] 允许空吗？
没用Outer join 不应该有null

[realgz]

语句太晕。。。。
贴出表结构吧。。。整理了一下语句花了3分钟。。先把语句给你，自己好好看下逻辑有没有错：
select brand,linename,productref,description,ordered_items_bak.[buying price],ordered_items_bak.[public price]

from orders,ordered_items_bak ,[product information],item_specification

where orders.[order ref] = ordered_items_bak.[order ref] and

[product information].[productref] = ordered_items_bak.[product reference] and

ordered_items_bak.[product reference] = item_specification.[product reference] and
ordered_items_bak.[order ref] = item_specification.[order ref] and

item_specification.[order ref] = 'Sp0101123186'

group by
[orders].brand,
[product information].linename,
[product information].productref,
[product information].description,
[public price],[public price2],
[public price3],[buying price],
ordered_items_bak.[order ref]
order by
orders.brand,[product information].linename,[product information].productref

[ghosthjt]

下次发贴的时候，记得选排好版再发，要不然人家都懒得看这密密麻麻的语句.